Combining LINQ Queries to reduce database calls

Question

I have 2 queries that work, I was hoping to combine them to reduce the database calls.

                var locations = from l in db.Locations
                                where l.LocationID.Equals(TagID)
                                select l;

I do the above because I need l.Name, but is there a way to take the above results and put them into the query below?

                articles = from a in db.Articles
                               where
                               (
                               from l in a.Locations
                               where l.LocationID.Equals(TagID)
                               select l
                               ).Any()
                               select a;

Will I actually be reducing any database calls here?

Can you turn that into a join? Or is it many locations per tag ID? — Rup, May 25 '13 at 12:57
TagID will be only a single location, A join sounds like the right way, I'm just not skilled enough in Linq to make it happen! — Robbie Mills, May 25 '13 at 13:16

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

This seems a bit complicated because Locations appears to be a multi-value property of Articles and you want to only load the correct one. According to this answer to a similar question you need to use a select to return them separately in one go so e.g.

var articles = from a in db.Articles
               select new {
                   Article = a,
                   Location = a.Locations.Where(l => l.LocationId == TagId)
               };

First failed attempt using join:

var articlesAndLocations = from a in db.Articles
                           join l in a.Locations
                             on l.LocationID equals TagID
                           select new { Article = a, Location = l };

(I usually use the other LINQ syntax though so apologies if I've done something stupid there.)

edited May 23 '17 at 12:11

Community

1
1

answered May 25 '13 at 13:38

Rup

33,765
9
83
112

Thanks but doesn't seem to work - I can an error on the 'a' on the second line, that "The name 'a' does not exist in the current context. – Robbie Mills May 26 '13 at 11:47
Right, sorry, I was guessing there :-/ Second try, something closer to the linked answer, although I'm less confident this will be a single DB hit. If that fails I'm out of ideas, sorry. – Rup May 26 '13 at 12:22

dougajmcdonald · Answer 2 · 2013-05-26T13:01:46.540

0

Could you not use the Include() method here to pull in the locations which are associated with each article, then select both the article and location object? or the properties you need from each.

The include method will ensure that you don't need to dip into the db twice, but will allow you to access properties on related entities.

You would need to use a contains method on an IEnumerable I believe, something like this:

var tagIdList = new List() { TagID };

var articles = from a in db.Articles.Include("Locations")
           where tagIdList.Contains(from l in a.Locations select l.LocationID)
           select new { a, a.Locations.Name };

(Untested)

edited May 26 '13 at 13:01

answered May 26 '13 at 12:34

dougajmcdonald

19,231
12
56
89

Looks promising, but I get an error on Locations.LocationID on the second line: System.Collections.Generic.ICollection' does not contain a definition for 'LocationID' and no extension method 'LocationID' accepting a first argument of type 'System.Collections.Generic.ICollection' could be found (are you missing a using directive or an assembly reference?) – Robbie Mills May 26 '13 at 12:40
Ah right, in that case you need to slim down your locations to a single record, or use a contains statement. – dougajmcdonald May 26 '13 at 12:51

Combining LINQ Queries to reduce database calls

2 Answers2