12

I'm using EF (dll version is 4.4) to query against a database. The database contains several tables with course information. When having a look what actually is sent to the db I see a massive, almost 1300 line SQL query (which I'm not going to paste here because of it's size). The query I'm running on the context looks like:

entities.Plans .Include("program") .Include("program.offers") .Include("program.fees") .Include("program.intakes") .Include("program.requirements") .Include("program.codes") .Include("focuses") .Include("codes") .Include("exceptions") .Include("requirements") where plans.Code == planCode select plans).SingleOrDefault();

I want to avoid having to go back to the server when collecting information from each of the related tables but with such a large query I'm wondering if there is there a better way of doing this?

Thanks.

Improve this question
8
  • Well you're doing eager loading and your query is attempting to get all the data at once, thus why it's large. What exactly do you see as a problem there? Other option is to do lazy loading with virtual properties, that will be fetched in the background by EF when you try to access them, but you did specify you don't want roundtrips to database. Commented Mar 21, 2013 at 6:36
  • There's a kind of similar answer here stackoverflow.com/questions/5521749/… Commented Mar 21, 2013 at 6:37
  • I guess I was wondering if I'm looking at this correctly. One huge query vs several small queries or whether there is a better way of doing this. Commented Mar 21, 2013 at 8:16
  • do you really need all this information? what are you going to do with it? A human being will probably not able to comprehend it/process it but it might make sense for some automated processing. I think I would ask myself - if I was not using EF (where creating this kind of query is easy) but would have to write SQL by myself - would I create such a query or would I find what I really need and get this information from the database and send additional queries when needed? (9 joins is quite a lot but might be OK if can be justified) Commented Mar 21, 2013 at 20:12
  • The information from the other tables is aggregated to display as short summary with course information, so yes I really need it. Commented Mar 21, 2013 at 21:56

5 Answers 5

Reset to default
1

A bit late but, as your data is only changing once a day, look at putting everything you need into an indexed view and place this view in your EF model.

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

Comments

0

You can usually add the .Include() after the where clause. This means that you're only pulling out information that matches what you want, see if that reduces your query any.

Improve this answer

2 Comments

This does not make a difference. I ran the query in LINQPad and the SQL generated is the same in both cases.
after the where clause and it was still the same? Seems odd...Only other thing I can suggest is to try navigation properties, I believe they work slightly different, but I honestly don't fully understand them. Alternatively, you could pull out the information from the plans table, with the filter/where clause. Then run a few join statements to get what you need. Code isn't quite a succinct, but it should speed things up.
0

As you are performing an eager loading, so if you are choosing the required entities then its fine. Otherwise you can go with Lazy Loading, but as you specified that you don't want database round trip, so you can avoid it.

I would suggest, If this query is used multiple times then you can use compiled query. So that it will increase the performance.

Go through this link, if you want.. http://msdn.microsoft.com/en-us/library/bb896297.aspx

Improve this answer

Comments

0

If you're using DbContext, you can use the .Local property on the context to look if your entity is already retrieved and therefore attached to the context.

If the query had run before and your root Plan entities are already attached based on Plan.Code == planId, presumably its sub-entities are also already attached since you did eager loading, so referring to them via the navigation properties won't hit the DB for them again during the lifetime of that context.

This article may be helpful in using .Local.

Improve this answer

Comments

0

You may be able to get a slightly more concise SQL query by using projection rather than Include to pull back your referenced entities:

var planAggregate = (from plan in entities.Plans let program = plan.Program let offers = program.Offers let fees = program.Fees //... where plan.Code == planCode select new { plan program, offers, fees, //... }) .SingleOrDefault();

If you disable lazy loading on your context this kind of query will result in the navigation properties of your entities being populated with the entities which were included in your query.

(I've only tested this on EF.dll v5.0, but it should behave the same on EF.dll v4.4, which is just EF5 on .NET 4.0. When I tested using this pattern rather than Include on a similarly shaped query it cut about 70 lines off of 500 lines of SQL. Your mileage may vary.)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.