Entity Framework SQL query not returning correct data

Question

I am using EF6 and have a query using projection. I got the code from a question I asked here:

This code works perfectly, however it generates over 1200 lines of SQL and on first call, it is taking over 5 seconds to compile the query and 300ms to execute it, which is not acceptable. You can see what I mean here:

I used Glimpse to see what the SQL it was generating and interestingly noticed that if I remove comments, the SQL goes down to just 200 lines of code.

I decided to place the SQL into a stored procedure and although it returns the posts data correctly, the PostAuthor is not being populated.

I double checked and with the Linq query it is populated, but using

_context.Posts.SqlQuery(...)

the PostAuthor object is not being populated.

I also double checked the raw SQL query in SQL Server Management Studio and all the data is being returned, it is just not being populated when it returns.

Am I missing something? Is this a known issue?

You might need to show some code to help. What is the C# LINQ query you are using? What is the SQL of the generated query in Glimpse? What is in the dots in your alternative solution? — David Spence
– David Spence, Commented Dec 2, 2015 at 16:55
If I recall correctly (because I don't use stored procs like that), it's a known issue based on some other threads I've read. — Robert McKee
– Robert McKee, Commented Dec 2, 2015 at 17:31

Robert McKee · Accepted Answer · 2015-12-02 17:49:17Z

0

Try this instead:

var allposts = _context.Posts
  .Include(p=>p.Comments)
  .Include(p=>p.Attachments)
  .Where(t => t.PostAuthor.Id == postAuthorId)
  .ToList();

Of course, posting the SQL could help in determining if there might be a better approach to retrieving your data as well.

edited Dec 2, 2015 at 17:49

answered Dec 2, 2015 at 17:40

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

grayson Over a year ago

Thanks for the answer, unfortunately I need to only get comments with a particular sender and attachments with a particular owner so I cant use this. This was the basis of my other question,

Robert McKee Over a year ago

And is it faster than the above?

Collectives™ on Stack Overflow

Entity Framework SQL query not returning correct data

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related