How to run dynamic SQL query in Entity Framework 7

问题:

I have an existing app that I am trying to upgrade from MVC5/EF6 to MVC6/EF7. We dynamically create some of our SQL tables, and as a result, have made use of the

System.Data.Entity.Database.SqlQuery

method to automatically map to entities that we use throughout our application.

This method seems to have gone away (i.e. not part of Microsoft.Data.Entity.Infrastructure.Database ) in EF7 (or is not yet implemented). Are there plans to re-implement this method in EF7 or is there another way to accomplish this? Our project is kind of dead in the water until we figure this out.

Edited on May 20, 2015

I've been trying to make this work with FromSql, since that's what's available in Beta4, but no matter what combination of concatenated string, parameters I try, I keep getting different versions of an "Incorrect Syntax near @xxxvariable" message.

var results = Set<AssessmentResult>().FromSql("dbo.GetAssessmentResults @FieldA='Data1', @FieldB='Data2', @UserId = 2303"); 
var results2 = Set<AssessmentResult>().FromSql("dbo.GetAssessmentResults @FieldA= {0}", intData); 

Both of these calls result in

"Incorrect syntax near '@FieldA'" 

Any ideas?

回答1:

We recently introduced the .FromSql() extension method on DbSet. It has the added benefit that you can continue composing LINQ on top of it.

var customers = db.Customers
    .FromSql("SELECT * FROM Customer")
    .Where(c => c.Name.StartsWith("A"));