0

So far, I've been using classic ADO.NET model for database access. I have to tell you that I'm quite happy with it. But I have also been hearing much about Entity Framework recently so I thought I could give it a try. Actually the main reason which pushed me was the need to find a way to build the WHERE clause of my Stored Procedures. With the classic way I have to do either of the following:

  1. Build the WHERE clause in the client side based on the user inputs and send it as a VARCHAR2 argument to the Stored Procedure, concatenate the WHERE clausewith the main part of the SQL and pass the whole string to EXECUTE_IMMEDIATE function. I personally hate to have to do so.
  2. Inside the Stored Procedure construct lots and lots of SQL statements, which means I have to take all the possible combinations that WHERE clause might be composed of into account. This seems worse than the first case.

I know that EF has made it possible to use Stored Procedures as well. But will it be possible to build the WHERE part dynamically? Can EF rescue me somehow?

Mikayil Abdullayev
  • 12,117
  • 26
  • 122
  • 206

1 Answers1

1

yes, you can use Dynamic queries in Linq.

  1. Dynamic Query LIbrary

from scott gu example 

var query = Northwind.Products.Where("Lastname LIKE "someValue%");

or some complex query 

var query =
    db.Customers.
    Where("City = @0 and Orders.Count >= @1", "London", 10).
    OrderBy("CompanyName").
    Select("new(CompanyName as Name, Phone)");

or from this answer Where clause dynamically.

var pr = PredicateBuilder.False<User>();
foreach (var name in names)
{
    pr = pr.Or(x => x.Name == name && x.Username == name);
}
return query.AsExpandable().Where(pr);
Community
  • 1
  • 1
Ravi Gadag
  • 15,735
  • 5
  • 57
  • 83
  • But this is just creating a query dynamically. I want to use stored procedures. – Mikayil Abdullayev Feb 19 '13 at 08:42
  • @MikeJM try chaining where method for Stored Procedure. something like this. yourContext.yourSPname().Where(here your query); // just pseudocode, not sure whether work or not – Ravi Gadag Feb 19 '13 at 08:46