Building dynamic Linq to SQL statements for the entity framework Lamda

Question

User1034446946 posted

Hi

Up until now I am building an asp.net mvc application using the entity framework, and until now I have only needed a fairly simple set of where statements to do everything i need.

However I now need to perform more complex search using alot more "or" statements instead of all the "and" statements

So i need to start building more dynamic statements

I am currently using expression to loop into a where clause

What i am interested in, is whats considered the current best way to achive more dynamic linq to sql statements?

Links to tutorials would be appriciated but any suggestes would also be appriciated.

Answer 1

User-821857111 posted

You can build the LINQ statement in the same way as you would build a SQL statement with optional parameters. E.g.

var result = db.Table.Where(t =>
    (param1 == null || t.field1 == param1) &&
    (param2 == null || t.field2 == param2) &&
    (param3 == null || t.field3 == param23)//etc

If you need to determine whether to use && or ||, you first need to establish whether the user wants an AND or and OR clause, and then you build up the statement conditionally. Once you have the final statement, then you call ToList on it to actually get the SQL generated and executed against the database.

Answer 2

User1034446946 posted

Thanks for the reply, I problem isn't knowing when to use and or and or, its how I can set everything up to allow it.

At present I build expressions (of which I have limited knowledge), i send the expression to a repository which loops through the expressions and puts them each into an there own where clause. and uses IQueryable.

I like this approach as it allows me to see whats happening in the service layer, my issue is i know need to add a lot of or statements as the project has got to a more advanced stage, building expressions in the way I have been causes error.

So i was just wondering if there is something i don't know about I can adopt or something i can look into to create a better pattern to achieve what i need.

Answer 3

User1034446946 posted

hi i am thinking about using

http://www.albahari.com/nutshell/predicatebuilder.aspx

or similar

but can't find a tutorial which holds my hand enough and gives me enough examples to understand it properly

Answer 4

User1120430333 posted

but can't find a tutorial which holds my hand enough and gives me enough examples to understand it properly

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/entity-sql-overview

https://msdn.microsoft.com/library/bb738521(v=vs.100).aspx

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/how-to-execute-a-parameterized-entity-sql-query-using-entitycommand

You could use dynamic Entity SQL. You know, the whole world is not centered around Linq, and Linq is not a stops all, ends all and holier than thou solution.

Answer 5

User1034446946 posted

but can't find a tutorial which holds my hand enough and gives me enough examples to understand it properly

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/entity-sql-overview

https://msdn.microsoft.com/library/bb738521(v=vs.100).aspx

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/how-to-execute-a-parameterized-entity-sql-query-using-entitycommand

You could use dynamic Entity SQL. You know, the whole world is not centered around Linq, and Linq is not a stops all, ends all and holier than thou solution.

Thanks for the info, none of those do what I am after, and while i know there is a beyond linq, i really like linq, i find it easy to understand and use, and I don't want to have to learn SQL at this time.

I will read through them properly though.

Answer 6

User475983607 posted

At present I build expressions (of which I have limited knowledge), i send the expression to a repository which loops through the expressions and puts them each into an there own where clause. and uses IQueryable.

This seems odd to me.  First, you're building a repo on a repo as EF is already a Unit of Work.  Then it seems like you are trying to extend the repo from its interface into basically an entity.  Why not just use EF as is?

Seems like you are going in circles.  Can you provide a use case or a real world example that explains the problem you are trying to solve?

Answer 7

User1034446946 posted

i am using the entity framework, i don't need to extend my repo, although happy to do it if needed.

i just need a way to add "or" clauses dynamically to my expressions without it breaking, which it has upto now, although I think I have sorted it, maybe....

Answer 8

User1120430333 posted

DA924

but can't find a tutorial which holds my hand enough and gives me enough examples to understand it properly

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/entity-sql-overview

https://msdn.microsoft.com/library/bb738521(v=vs.100).aspx

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/how-to-execute-a-parameterized-entity-sql-query-using-entitycommand

You could use dynamic Entity SQL. You know, the whole world is not centered around Linq, and Linq is not a stops all, ends all and holier than thou solution.

Thanks for the info, none of those do what I am after, and while i know there is a beyond linq, i really like linq, i find it easy to understand and use, and I don't want to have to learn SQL at this time.

I will read through them properly though.

Not trying yo be smart here,  but it seems that those that are inexperienced, they always have the cart before the horse. You don't have DB Admin and T-SQL 101 skills, then how can you use an ORM effectively or know how to get out of trouble if you don't know the basics?

Answer 9

User1034446946 posted

I appriciate all the help, and have broad shoulders please don't worry about saying anything.

I am not a pro developer, and reading especially how programming docs are written I find very hard to understand especially when its new and when there is only one example path.

I know a little about DB admin, and I have to admit the EF and linq has made it so much quicker for me to implement things, you could say its either made me lazy or its enabled my to move forward quicker because I haven't spent time nailing down the basics, i just look to solve problems when they occur.

Efficency I will go back after developement and fine tune (happens all the time), getting out of trouble I do when it comes up (also happens all the time).

Answer 10

User1120430333 posted

Just using Linq with the ORM, you do know that T-SQL is generated by the ORM engine that is submitted to the DB engine, which the TSQL can be very inefficient where one has to adjust the Linq query to get more efficient T-SQL generated by the ORM. But how would you know this, if you don't have T-SQL 101 skills or DB Admin skills?  You should find out what a SQL Profiler is about.

Answer 11

User1034446946 posted

I know at some point I will have to move away from the EF and linq, and I know how I am doing things can be inefficient, but I have to focus on one thing at a  time (i am easily distracted), and i learning something new everyday, and am constantly finding better ways to do things.

My approach at this moment has to be get something working asap, inprove as things happen, i know this might not be the best approach but if I had to stop and learn everything seperate part of programming I would be swamped and never get any developement done.

However I will take the time to look into it further as soon as I can, and appriciate the insite.