How to run dynamic SQL query in Entity Framework 7 that auto-maps to Entities (SqlQuery)?

asp.net-core-mvc entity-framework entity-framework-core

Question

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?

Popular Answer

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


Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why