ASP.NET MVC 4 - Using Linq to read databse once & use multiple times

Question

I am using Linq to create 4 object collections:

var Restaurants = db.Places.Where(p => p.Tags.Any(t => t.Name == "Restaurant")).ToList();
var Bars = db.Places.Where(p => p.Tags.Any(t => t.Name == "Bar")).ToList();
var Pubs = db.Places.Where(p => p.Tags.Any(t => t.Name == "Pub")).ToList();
var Hotels = db.Places.Where(p => p.Tags.Any(t => t.Name == "Hotel")).ToList();

Obviously it's better to read once from the database, would the above open 4 connections with 4 queries on SQL Server 2012 or 1?

If I do the following instead, will Entity Framework only read from the database once, and what's the best way to test that myself?

var places = db.Places;
var Restaurants = places.Where(p => p.Tags.Any(t => t.Name == "Restaurant")).ToList();
var Bars = places.Where(p => p.Tags.Any(t => t.Name == "Bar")).ToList();
var Pubs = places.Where(p => p.Tags.Any(t => t.Name == "Pub")).ToList();
var Hotels = places.Where(p => p.Tags.Any(t => t.Name == "Hotel")).ToList();

What is Best Practice here? Are there any stats on this?

Thanks.

These appear to be equivalent. Both will result in four hits againsts the database. If you want them to be different then the start of the second code block should read: var places = db.places.ToList(); — TGnat
– TGnat, Commented Jun 9, 2013 at 4:38

Robert McKee · Accepted Answer · 2013-06-09 05:53:49Z

3

var types = new[]{"Restaurant","Bar","Pub","Hotel"};
var places = db.Places.Include(p=>p.Tags).Where(p=>p.Tags.Any(t=>types.Contains(t.Name))).ToList();

var Restaurants = places.Where(p => p.Tags.Any(t => t.Name == "Restaurant"));
var Bars = places.Where(p => p.Tags.Any(t => t.Name == "Bar"));
var Pubs = places.Where(p => p.Tags.Any(t => t.Name == "Pub"));
var Hotels = places.Where(p => p.Tags.Any(t => t.Name == "Hotel"));

Should result in only 1 query. Although, you are populating places with a hydrated Tag DbSet, which may significantly slow the query down. You could probably create a slightly different query that retrieved the Places and stored which type it was so you don't have to have a completely hydrated Tags DbSet, but I'll leave that to you if you find it necessary.

answered Jun 9, 2013 at 5:53

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

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

1 Comment

niico Over a year ago

useful mention of 'hydration' - I am new to this and will certainly research that too...

StriplingWarrior · Accepted Answer · 2013-06-10 16:04:56Z

2

You can trick Entity Framework into loading these as part of a single query, like so:

var q = 
    from p in db.Places
    group p by 1 into g
    select new {
        Restaurants = g.Where(p => p.Tags.Any(t => t.Name == "Restaurant")),
        Bars = g.Where(p => p.Tags.Any(t => t.Name == "Bar")),
        Pubs = g.Where(p => p.Tags.Any(t => t.Name == "Pub")),
        Hotels = g.Where(p => p.Tags.Any(t => t.Name == "Hotel"))
    }
var results = q.Single();

edited Jun 10, 2013 at 16:04

answered Jun 9, 2013 at 4:58

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

2 Comments

niico Over a year ago

very nice - so I would access those like this? q.Restaurants - etc?

StriplingWarrior Over a year ago

@niico: I updated the example to indicate that you want to get the single object that this query should return (since it's grouped by a constant). Then yes, you could access results.Restaurants, etc.

Ortiga · Accepted Answer · 2013-06-09 04:57:14Z

Both of your codes results in four queries to DB.

In NHibernate, you can do that using future:

var Restaurants = places.Where(p => p.Tags.Any(t => t.Name == "Restaurant")).Future<Place>();
var Bars = places.Where(p => p.Tags.Any(t => t.Name == "Bar")).Future<Place>();
var Pubs = places.Where(p => p.Tags.Any(t => t.Name == "Pub")).Future<Place>();
var Hotels = places.Where(p => p.Tags.Any(t => t.Name == "Hotel")).Future<Place>();

And them NHibernate will set in four queries in one round-trip when the any of them request the result, as long as database supports it.

I don't think EF currently has similar feature, but in some cases like you've shown, you can OR the clauses:

var myPlaces = places.Where(p => p.Tags.Any(t => t.Name == "Restaurant")
    || p.Tags.Any(t => t.Name == "Bar")
    || t.Name == "Pub"
    || t.Name == "Hotel")).ToList();

Although you obviously can't get them separated by type, and will have to filter them again in memory. Whether or not this will result in a performance gain varies. If it's not sensible, I would recommend against it.

Collectives™ on Stack Overflow

ASP.NET MVC 4 - Using Linq to read databse once & use multiple times

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related