1

I am trying to display a grid of Orders from the Northwind sample database, which has FK references to the Customers and Employees tables. I want to use raw SQL (variable "query") and project to a ViewModel "Order_VM". The ViewModel includes the references to the Customers and Employees entities.

I have tried the following statement, which works fine, but only with the original model "Order" and the DbSet version of SqlQuery:

IQueryable<Order> orders = db.Orders.SqlQuery(query).AsQueryable().Include(o => o.Customer).Include(o => o.Employee);

However, I want it to work with the ViewModel "Order_VM" instead (code below) using the Database version of SqlQuery (I assume this is the only way), but the CompanyName (from Customers table) and LastName (from Employees table) display blank in the View (grid).

IQueryable<Order_VM> orders = db.Database.SqlQuery<Order_VM>(query).AsQueryable().Include(o => o.Customer).Include(o => o.Employee);

I have tried with/without the "virtual" keyword for the Customer and Employee entity references in the ViewModel to see if eager vs lazy loading was a factor. However still get blanks. Thanks for any help as I am still learning.

mfsumption
  • 353
  • 2
  • 7

1 Answers1

0

I have tried the following statement, which works fine

No, it doesn't, because the Include doesn't do anything. It compiles and runs, but the returned type, DbSqlQuery<Order> doesn't have an implementation of Include, so the method is ignored. You tried to fix that with the AsQueryable call, but that only "re-activates" the extension method Include, but it doesn't magically inject an Include implementation.

DbSqlQuery (and DbRawSqlQuery, the return type of db.Database.SqlQuery) don't implement IQueryable<T>, so original EF query provider is out of scope and won't return.

To execute the Include statement it should be possible to modify the executed SQL statement and put some JOINs in there. But EF can only do that when Include is called on an object that contains an expression tree, so it can add expressions to this tree. A raw SQL query will never be parsed into an expression tree, but simply be executed as is.

So, these raw SQL queries are only capable of populating flat objects from the column list in the SELECT statement. Nested objects (references of collections) will never be materialized. If your view model has a CompanyName property, you should have a select statement like SELECT ..., Company.CompanyName, ..., so the raw SQL should contain the JOIN.

Community
  • 1
  • 1
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • well, it DOES work, somehow, because the grid does display the CompanyName and the LastName and I do not have them in the ViewModel or the raw SQL. I appreciate your explanation, but there must be some other answer. – mfsumption Oct 31 '14 at 19:09
  • The objects you obtain by the query are still capable of lazy loading. So if you monitor the SQL statements being sent to the database you'll see many queries that fetch individual objects. But that's very inefficient, of course. – Gert Arnold Oct 31 '14 at 19:12
  • So, what I am attempting to do appears to not be very practical or efficient, hmm, bummer. The ultimate goal behind the raw SQL experiment was to create a dynamic query mechanism. I am aware of the old DynamicLinq project from 2008 as mentioned by Scott Guthrie, but was not comfortable with that. Perhaps there is a better way for me to discover. Thx. – mfsumption Oct 31 '14 at 19:23
  • You can take a look at Entity SQL. Dynamic LINQ is still widely used though. – Gert Arnold Oct 31 '14 at 19:26