hdiazburgos / lambda-sql-builder Goto Github PK

        [LambdaSqlBuilder.SqlLamTable(Name = "Test")]
        public class Test
        {
            [LambdaSqlBuilder.SqlLamColumn(Name = "a")]
            public short a {get; set;}
        }
        public void TestFunction()
        {
            Test c = new Test();
            var query = new SqlLam<Test>()
                .Where(t => t.a == c.a);
        }

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: ambiguous exception
LambdaSqlBuilder.Resolver.LambdaResolver.BuildSql(LambdaSqlBuilder.Resolver.Expr
essionTree.SingleOperationNode, LambdaSqlBuilder.Resolver.ExpressionTree.Node, 
System.Linq.Expressions.ExpressionType)
LambdaSqlBuilder.Resolver.LambdaResolver.BuildSql(LambdaSqlBuilder.Resolver.Expr
essionTree.Node, LambdaSqlBuilder.Resolver.ExpressionTree.SingleOperationNode, 
System.Linq.Expressions.ExpressionType)

* Added support for customizing SqlLam behavior.
* Added customization of SqlLam to populate SqlBuilder's selection list with 
all of
 the entity type's columns aliased with the property name, to facilitate the
 mapping of the result set to the property when executing a query via Dapper.

To see what the patch will do:

git apply --stat select-explicit-column-names.patch

To test applying:

git apply --check select-explicit-column-names.patch

To signoff and apply:

git am --signoff < select-explicit-column-names.patch

What steps will reproduce the problem?
 public static List<T> GetPage<T>(Expression<Func<T, bool>> expression, Expression<Func<T, object>> orderBy, int pageSize, int pageNumber = 1) where T : new()
        {
            var connection = new SqlConnection("Data Source=10.0.0.6;Initial Catalog=Testt;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=master;Connect Timeout=120;");

            var query = new SqlLam<T>(expression);
            query.OrderBy(orderBy);
            query.QueryStringPage(pageSize, pageNumber);


            var selectCommand = new SqlCommand(query.QueryString, connection);

            foreach (var param in query.QueryParameters)
                selectCommand.Parameters.AddWithValue(param.Key, param.Value);

            connection.Open();
            var result = selectCommand.ExecuteReader();
            var premios = Extensions.Map<T>(result);
            connection.Close();

            return premios;
        }

What is the expected output? What do you see instead?
List paginate 

What version of the product are you using? On what operating system?
1.0.0.0

Please provide any additional information below.

Not really a issue but an improvment.

Iam using Dapper and querying multiple related tables (join with pagging).

With sql server 2008 (doesn't support new feature Offset of SQL server 2012), 
as I have some columns of different tables i get an error executing the query.

My solution would be an implementation for column alias support.
There exists any other solution?

Thanks for this great library.

Take the following query..


var name = "test";
var sql = new SqlLam<User>();
sql.Where(x => x.UserName == name).Or(x => name == null);
or
var sql = new SqlLam<User>(x => x.UserName == name || name == null);


This causes a System.StackOverflowException, what it should return is
"SELECT * FROM User WHERE Username = @Param1 OR @Param1 IS NULL"

Check out the code, try to run tests.  I located a northwind db, but it isn't 
the same one and the tests fail.  it would be useful to have the same one you 
provided

First of all, great job on this, I was actually doing it myself as well, but 
ended up getting extremely messy and did not get as far as you did, ended up 
scrapping it and using this.

Only issue I am having is the following query:

query.Where(x => (x.IsActive || x.IsLocked) && x.EmailAddress == 
"[email protected]");

Is giving the following error:

System.Collections.Generic.KeyNotFoundException : The given key was not present 
in the dictionary.

at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at Resolver.LambdaResolver.BuildSql(MemberNode leftMember, MemberNode rightMember, ExpressionType op) in LambdaResolverTree.cs: line 80
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid4<T0,T1,T2,T3>(CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3)
   at Resolver.LambdaResolver.BuildSql(OperationNode node) in LambdaResolverTree.cs: line 46
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2<T0,T1>(CallSite site, T0 arg0, T1 arg1)
   at Resolver.LambdaResolver.BuildSql(Node leftNode, Node rightNode, ExpressionType op) in LambdaResolverTree.cs: line 99
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid4<T0,T1,T2,T3>(CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3)
   at Resolver.LambdaResolver.BuildSql(OperationNode node) in LambdaResolverTree.cs: line 46
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2<T0,T1>(CallSite site, T0 arg0, T1 arg1)
   at Resolver.LambdaResolver.ResolveQuery(Expression`1 expression) in LambdaResolverQuery.cs: line 18
   at LambdaSqlBuilder.SqlLam`1.And(Expression`1 expression) in SqlLam.cs: line 47
   at LambdaSqlBuilder.SqlLam`1.Where(Expression`1 expression) in SqlLam.cs: line 41
   at Tests.ExpressionTests.WhereMultipleBoolAndElse_Should_ReturnQuery() in ExpressionTests.cs: line 364


The only workaround I was able to do is add "== true" to both booleans, however 
if I only did 1 boolean, it works fine

Example..
query.Where(x => (x.IsActive || x.CreatedOn == date) && x.EmailAddress == 
"[email protected]");

This one works without issue