CODEWITHSUNDEEP
c#linqentity-frameworkentity-framework-6
JTunney
JTunney

Reputation: 904

Passing a query directly through LINQ to the db

I am currently converting functions in the DAL of an old app to a new app using Entity Framework/LINQ in its DAL.

There are some instances where I would like to directly pass a string of sql to the database. Is this possible when using LINQ? Here is what I tried atfer research but ExecuteQuery is 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();
    }

Doing this seems pretty straight forward but ExecuteQuery is not available to me.

Here is my next attempt at it which seems much better: (please tell me if there is a better way) 

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();

Upvotes: 0

Views: 3363

Answers (3)

Parimal Raj
Parimal Raj

Reputation: 20585

Although for your current condition you can use LINQ.

var customer = context.Customers.Where(c => c.CustomerId = id).Distinct();

This is how you do it Entity Framework Raw SQL Queries

Sending raw commands to the database

using (var context = new DbContext()) 
{ 
    context.Database.SqlCommand( 
        "UPDATE dbo.Blogs SET Name = 'Another Name' WHERE BlogId = 1"); 
}

Writing SQL queries for entities

using (var context = new DbContext()) 
{ 
    var customers = context.Customers.SqlQuery("SELECT * FROM dbo.Customers").ToList(); 
}

Loading entities from stored procedures

using (var context = new DbContext()) 
{ 
    var customers = context.Blogs.SqlQuery("dbo.GE_Customers").ToList(); 
}

Writing SQL queries for non-entity types

using (var context = new DbContext()) 
{ 
    var customerNames = context.Database.SqlQuery<string>( 
                       "SELECT Name FROM dbo.Customers").ToList(); 
}

Update to answer

You do not need to pass SqlParameter, simply pass the default objects

I think the code below should work fine.

var customerList = context.Customers.SqlQuery(sql.ToString(), customerId, customerName).ToList();

if your real query is

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

I would suggest you to do it this way

var customers = context.Costomers; // this does not populates the result yet

if (!String.IsNullOrEmpty(customerId))
{
    customers = customers.Where(c => c.CustomerId = customerId); // this does not populates the result yet
}

if (!String.IsNullOrEmpty(customerName))
{
    customers = customers.Where(c => c.CustomerName = customerName); // this does not populates the result yet
}

// finally execute the query
var custList = customers.ToList();

Upvotes: 2

D Stanley
D Stanley

Reputation: 152594

You can use SqlQuery, but it would be better to pass the parameter instead of using Format:

  var sql = @"SELECT DISTINCT * FROM Customer where CustomerId = {0}";
  var query = DbContext.SqlSuery<Customer>(sql, customerId);

That way, the parameters are SQL encoded so as not to allow for SQL Injection.

Upvotes: 1

Marc Cals
Marc Cals

Reputation: 2989

If there is not limitation or requirement don't use this kind of queries they are vulnerable to sql inject attacks.

You can do nearly every sentence with Entity Framework using linq, like the one you wrote

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

It's more readable, and more secure.

Upvotes: 2

Related Questions