Passing a query directly through LINQ to the db

c# entity-framework entity-framework-6 linq


I'm in the process of transferring DAL functionalities from an outdated project to a new app that uses Entity Framework/LINQ in its DAL.

In some circumstances, I would like to send a string of SQL directly to the database. Is LINQ capable of accomplishing this? After doing some investigation, I attempted the following, however ExecuteQuery was not available.

    using (var context = new DbContext())
      var sql = @"SELECT DISTINCT * FROM Customer where CustomerId = {0}";
      sql = string.Format(sql, customerId);
      var query = DbContext.ExecuteQuery<Customer>(sql);
      return query.ToList();

Although it sounds very simple to do, I don't have access to ExecuteQuery.

Here's my updated effort, which I think is much better: (If there is a better way, kindly let me know)

StringBuilder sql = new StringBuilder();

sql.AppendLine("SELECT * FROM CUSTOMERS ");
sql.AppendLine("WHERE @CustomerId = null OR CustomerId = @CustomerId ");
sql.AppendLine("AND @CustomerName = null OR CustomerName = @CustomerName ");

var customerList = context.Customers.SqlQuery(sql.ToString(),
                                                 new SqlParameter("@CustomerId", customerId),
                                                 new SqlParameter("@CustomerName", customerName)).ToList();
6/8/2015 8:55:27 PM

Accepted Answer

6/8/2015 9:32:36 PM

Popular Answer

Avoid using these kind of queries if there are no restrictions or requirements since they can be attacked using SQL injection.

With Entity Framework and Linq, practically every phrase, including the one you just wrote, is possible.

DbContext.Customer.Where(c => c.CustomerId = id).Distinct();

It is safer and easier to read.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow