LambdaToSql-Beta 1.0.0

LambdaToSQL Beta v0.1 Documentation

Report any bug at lambdatosql@gmail.com

Disclaimer: The purpose of this library is not to replace SQL scripting, but to help C# engineers write SQL at a high level with no regard for the order, SQL is a Structural Query Language that forces developers to follow specific syntax and order. Yes SQL is that rigid, Having cannot come before the Group by that how rigid SQL script is, certain operations follow a specific order and pattern

Note: This version only supports T-SQL (SQL Server) other flavours of SQL will be added as we move on in this journey

Helper Attributes

Helper attributes are property and class attributes that helps the resolver map relationship between objects and also help for customisation of your query i.e (table alias, specifying primary key, naming your table and aslo use for ignoring specific columns from the resolver)

TableName

AttributeUsage(AttributeTargets.Class, Inherited = true, AllowMultiple = false)]

    [TableName(tableName: "User", alias: "usr")]
    public class User
    {
        //props goes here
    }

Pk (Primary Key)

AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]

  [TableName(tableName: "User", alias: "usr")]
    public class User
    {
        [Pk("id")] // Primary key for the table/object the resolver uses this attribute to dectect property name
        public int id { get; set; }
        public string email { get; set; }
        public string name { get; set; }
    }

Relation

AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]

    [TableName(tableName: "Product", alias: "pro")]
    public class Product
    {
        [Pk("id")] // primary key
        public int id { get; set; }
        public string product_name { get; set; }
        public int qty { get; set; }
        [Relation(typeof(Category))] // create relationship with Category object
        public int categoryId { get; set; } // foriegn key [Category object primary key]
        [Relation(typeof(ProductExtraDetails))] // create relationship with ProductExtraDetails object
        public int extraId { get; set; } // // foriegn key [ProductExtraDetails object primary key]

    }

IgnoreMe

AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]

  [TableName(tableName: "Product", alias: "pro")]
    public class Product
    {
        [Pk("id")]
        [IgnoreMe] // Igore id from being selected
        public int id { get; set; }
        public string product_name { get; set; }
        public int qty { get; set; }
        [Relation(typeof(Category))]
        public int categoryId { get; set; }
        [Relation(typeof(ProductExtraDetails))]
        public int extraId { get; set; }

    }

Utility Methods

• Select()
• Where()
• OrWhere()
• OrderBy()
• GroupBy()
• Having()
• Count()
• Limit()
• ToSQL()
• InnerJoin()
• LeftJoin()
• RightJoin()

Throughout our sample code we are going to reference these objects to build our queries

 [TableName(tableName: "Product", alias: "pro")]
    public class Product
    {
        [Pk("id")]
        [IgnoreMe]
        public int id { get; set; }
        public string product_name { get; set; }
        public int qty { get; set; }
        [Relation(typeof(Category))]
        public int categoryId { get; set; }
        [Relation(typeof(ProductExtraDetails))]
        public int extraId { get; set; }
        public DateTime createdAt {get;set;}
    }

    [TableName(tableName: "Category", alias: "cat")]
    public class Category
    {
        [IgnoreMe]
        [Pk("id")]
        public int id { get; set; }
        public string name { get; set; }
        public string description { get; set; }
    }

    [TableName(tableName: "User", alias: "usr")]
    public class User
    {
        [Pk("id")]
        [IgnoreMe]
        public int id { get; set; }
        public string email { get; set; }
        public string name { get; set; }
    }

    [TableName(tableName: "UserCart", alias: "uca")]
    public class UserCart
    {
        [Pk("id")]
        public int id { get; set; }
        [Relation(typeof(User))]
        public int userId { get; set; }
        [Relation(typeof(Product))]
        public int productId { get; set; }
    }

    [TableName(tableName: "ProductExtraDetails", alias: "pre")]
    public class ProductExtraDetails
    {
        [Pk("id")]
        [IgnoreMe]
        public int id { get; set; }
        public string more { get; set; }
    }

Select([optional] params string[] columns)

The entry and exist method for all queries start with the Select() and end with ToSQL() methods/function

if columns is not passed the resolver uses all property not flagged as ignore as select columns Sample code

   var sql1 = new UserCart() // My object
                .Select("uac.productid", "uac.userid", "sum(uac.productid) as total") // The resolver won't bother to resolve the columns it uses the passed columns and function
                .ToSQL(); //ToSQL execute the code and generate the SQL plain query

   var sql2 = new UserCart() // My object
                .Select() // columns is not passed resolver uses object property that are not flagged as ignoreMe
                .ToSQL(); // ToSQL execute the code and generate the SQL plain query

Results

 -- sql1 result   
    SELECT uac.productid,uac.userid,sum(uac.productid) as total FROM UserCart AS uca
--  sql2 result
    SELECT uca.id,uca.userId,uca.productId FROM UserCart AS uca

Where< T >([required] Expression<Func<T, bool>> where)

T is the object that you want to apply the filter clause to, passing T enables strong intellisence on the lambda expression.

Sample code

   var sql1 = new UserCart() // My object
                .Select("uac.productid", "uac.userid", "max(uac.productid) as maxid") ion
                .Where<UserCart>(x=>x.userId == 23 && x.productId > 78)
                .ToSQL();

   var sql2 = new UserCart()
                .Select() 
                .Where<UserCart>(x=>x.userId == 23 && x.productId > 78)
                .ToSQL();

   var items = new List<int>() { 3, 6, 5, 2 };
   var date = DateTime.Now             
   var sql3 = new Product()
            .Select()
            .Where<Product>(x=>x.categoryId == 23 || !items.Contains(x.extraId) && x.createdAt >= date)
            .ToSQL();

Results

 -- sql1 result   
    SELECT uac.productid,uac.userid,max(uac.productid) as maxid FROM UserCart AS uca WHERE ((uca.userId = 23) AND (uca.productId > 78)) 
--  sql2 result
    SELECT uca.id,uca.userId,uca.productId FROM UserCart AS uca WHERE ((uca.userId = 23) AND (uca.productId > 78)) 
--- sql3 result
    SELECT pro.product_name,pro.qty,pro.categoryId,pro.extraId,pro.createdAt  FROM Product AS pro WHERE ((pro.categoryId = 23) OR (NOT (pro.extraId IN (3,6,3,2)) AND (pro.createdAt >= '19/12/2022 03:52:45'))) 

Join(Type from, [optional] Type to)

The join type comes with different flavours

• InnerJoin(Type from, [optional] Type to)
• LeftJoin(Type from, [optional] Type to)
• RightJoin(Type from, [optional] Type to)

    var items = new List<int>() { 3, 6, 7, 2 };
    var sql1 = new UserCart().Select()
               .Where<UserCart>(x => x.productId == 3 || items.Contains(x.userId))
               .InnerJoin(typeof(Product)) // Join UserCart object with Product object
               .Where<Product>(x => !items.Contains(x.extraId) && x.categoryId != 4)
               .InnerJoin(typeof(ProductExtraDetails), typeof(Product)) //Join ProductEtraDetails object   with Product object
               .LeftJoin(typeof(User)) // Join UserCart object with User object
               .OrWhere<User>(x => x.id == 5)
               .ToSQL();

    var date = DateTime.Now.Date;
    // The order is not as rigid as SQL the order doesnt matter when using LambdaToSQL
    var sql2 = new Product() 
                .Select("pro.categoryId","count(*) as count", "sum(pro.qty) as totalQty")
                .Where<Product>(x=>x.categoryId == 23 ||
                !items.Contains(x.extraId)
                && x.createdAt >= date
                )
                .Having("count(*) > 12 AND sum(pro.qty) > 10") 
                .OrderBy(LambdaToSQL.Enums.OrderBy.DESC, "pro.categoryId")
                .GroupBy("pro.categoryId")
                .InnerJoin(typeof(Category)) 
                .ToSQL();

Results

 -- sql1 result   
SELECT
  uca.id,
  uca.userId,
  uca.productId,
  pro.product_name,
  pro.qty,
  pro.categoryId,
  pro.extraId,
  pro.createdAt,
  pre.more,
  usr.email,
  usr.name
FROM
  UserCart AS uca
  INNER JOIN Product AS pro ON pro.id = uca.productId
  INNER JOIN ProductExtraDetails AS pre ON pre.id = pro.extraId
  LEFT JOIN User AS usr ON usr.id = uca.userId
WHERE
  (
    (uca.productId = 3)
    OR (uca.userId IN (3, 6, 7, 2))
  )
  And (
    NOT (pro.extraId IN (3, 6, 7, 2))
    AND (pro.categoryId <> 4)
  )
  OR (usr.id = 5)

  --sql2 result
SELECT
  pro.categoryId,
  count(*) as count,
  sum(pro.qty) as totalQty
FROM
  Product AS pro
  INNER JOIN Category AS cat ON cat.id = pro.categoryId
WHERE
  (
    (pro.categoryId = 23)
    OR (
      NOT (pro.extraId IN (3, 6, 7, 2))
      AND (pro.createdAt >= '19/12/2022 00:00:00')
    )
  )
GROUP BY
  pro.categoryId
HAVING
  count(*) > 12
  AND sum(pro.qty) > 10
ORDER BY
  pro.categoryId desc