CODEWITHSUNDEEP
c#entity-frameworklinq-to-sqlasp.net-mvc-4
user1477388
user1477388

Reputation: 21420

Reducing the number of trips to the database using EF and LINQ

Consider the following code:

// check record exists

Adjuster adj = new Adjuster();

if (db.Adjusters.Where(x => x.userID == user.id).Any())
{
    adj = db.Adjusters.Where(x => x.userID == user.id).FirstOrDefault();
}
else
{
    // create adjuster record
    adj.id = Guid.NewGuid();
    adj.userID = user.id;

    db.Adjusters.InsertOnSubmit(adj);
}

Notice how I first call .Any() and then I call .FirstOrDefault(). This would be two separate queries to the database, right? How can I limit this to just one?

Now, consider the following code:

adj.isPropertyAdjuster = user.SomeEntity.someProperty;
adj.isCasualityLiabilityAdjuster = user.SomeEntity.someProperty;
adj.isLargeLossAdjuster = user.SomeEntity.someProperty;

db.SubmitChanges();

Using LINQ/ EF, I can access different entities which have relationships with eachother. But, doesn't this incur multiple calls to the database, each time I do this? For instance, are all three of these separate calls to the database?

adj.isPropertyAdjuster = user.SomeEntity.someProperty;
adj.isCasualityLiabilityAdjuster = user.SomeEntity.someProperty;
adj.isLargeLossAdjuster = user.SomeEntity.someProperty;

How can I limit this to a singular call to the database? I assume the only way is to instantiate an object like so:

SomeEntity obj = user.SomeEntity;

And, then call the properties like so:

adj.isPropertyAdjuster = obj.someProperty;

What are your thoughts?

Upvotes: 2

Views: 911

Answers (3)

Jeremy Todd
Jeremy Todd

Reputation: 3289

For your first example, you can just call FirstOrDefault() and leave Any() out of the mix entirely:

Adjuster adj = db.Adjusters.Where(x => x.userID == user.id).FirstOrDefault();

if (adj == null)
{
  // create adjuster record
  adj.id = Guid.NewGuid();
  adj.userID = user.id;

  db.Adjusters.InsertOnSubmit(adj);
}

As for whether accessing a navigation property of an entity causes repeated round-trips to the database, the answer is (thankfully!) no. Depending on whether you have lazy loading enabled, the value of that property is either loaded along with the rest of the entity when it's first created, or else it's loaded the first time the property is accessed, and then cached for future use.

So, your example...

adj.isPropertyAdjuster = user.SomeEntity.someProperty;
adj.isCasualityLiabilityAdjuster = user.SomeEntity.someProperty;
adj.isLargeLossAdjuster = user.SomeEntity.someProperty;

...should only require at most one extra database query, and maybe not even that if you're using eager loading when you first load user.

Upvotes: 2

Daniel Moses
Daniel Moses

Reputation: 5858

You can fix the first code with

Adjuster adj = db.Adjusters.Where(x => x.userID == user.id).FirstOrDefault();

if (adj == null)
{
    // create adjuster record
    adj = new Adjuster();
    adj.id = Guid.NewGuid();
    adj.userID = user.id;

    db.Adjusters.InsertOnSubmit(adj);
}

And the second part is fine.

adj.isPropertyAdjuster = user.SomeEntity.someProperty;
adj.isCasualityLiabilityAdjuster = user.SomeEntity.someProperty;
adj.isLargeLossAdjuster = user.SomeEntity.someProperty;

db.SubmitChanges();

It shouldn't keep pulling someEntity from the database once it's loaded once. Also, it won't save anything until submit is called.

Upvotes: 2

Bobson
Bobson

Reputation: 13696

In regards to the first question:

var adjuster = db.Adjusters.FirstOrDefault(x => x.userID == user.id);
if (adjuster == null)
{
  // create adjuster record
    adjuster.id = Guid.NewGuid();
    adjuster.userID = user.id;

    db.Adjusters.InsertOnSubmit(adjuster);
}

In regards to the second, I believe EF caches data when it loads it, so your first access of user.SomeEntity.someProperty will hit the DB, but all subsequent ones won't. You might want to look into Eager Loading, though.

Upvotes: 5

Related Questions