is this EF6 code supposed to have queries in it?

Question

I'm trying to understand how to use Entity Framework 6. The code below works. However, it appears to have four queries in it for a single write operation. It doesn't seem right to hit the database five separate times. I want a single database call that adds the appropriate item to each table as needed. Is there some better way to do the code below? Or is it really doing a single database hit in the SaveChanges call?

public bool Write(ILogEntry logEntry)
{
    var log = logEntry as AssetStateLogEntry;
    if (log == null) return false;

    using (var db = _dbContextProvider.ConstructContext())
    {
        if (db != null)
        {
            var state = new VehicleStateLogEntryDbo
            {
                LogSource = db.LogSources.FirstOrDefault(l => l.Name == log.Source.ToString())
                    ?? new LogSourceDbo {Name = log.Source.ToString()}, 
                Message = log.Message, 
                TimeStamp = log.TimeStamp.ToUniversalTime(), 
                Vehicle = db.Vehicles.FirstOrDefault(v => v.Name == log.Asset.Name) 
                    ?? new VehicleDbo {Name = log.Asset.Name, VehicleIdentifier = log.Asset.ID}, 
                VehicleState = db.VehicleStates.FirstOrDefault(v => v.Name == log.StateValue.ToString() && v.VehicleStateType.Name == log.StateType.ToString())
                    ?? new VehicleStateDbo
                    {
                        Name = log.StateValue.ToString(),
                        VehicleStateType = db.VehicleStateCategories.FirstOrDefault(c => c.Name == log.StateType.ToString()) 
                            ?? new VehicleStateTypeDbo {Name = log.StateType.ToString()},
                    }
            };

            db.VehicleStateLogEntrys.Add(state);
            db.SaveChanges();
        }
    }
    return true;
}

score 2 · Accepted Answer · edited May 23 '17 at 12:13

You are indeed making 4 queries to the database, as a result of these calls:

db.LogSources.FirstOrDefault
db.Vehicles.FirstOrDefault
db.VehicleStates.FirstOrDefault
db.VehicleStateCategories.FirstOrDefault

When you call FirstOrDefault, the LINQ query is executed and thus, the database is hit.

I don't know your schema, but maybe you could join some of them into a single LINQ query (at least the Vehicles* tables seem to be related).

EDIT: sample query using joins as requested by the OP

Take the following query as an starting point of what I suggested, you haven't provided your entities so this is just to give you and idea:

from l in db.LogSources
join v in db.Vehicles on l.Asset.ID equals v.VehicleIdentifier 
join vs in db.VehicleStates on vs.VehicleIdentifier equals v.VehicleIdentifier
where l.Name == log.Source.ToString() 
   && v.Name == log.Asset.Name
   && vs.Name == log.StateValue.ToString() 
   && vs.VehicleStateType.Name == log.StateType.ToString()
select new VehicleStateLogEntryDbo
{
     LogSource = l,
     Message = log.Message,
     TimeStamp = log.TimeStamp.ToUniversalTime(),
     Vehicle = s,
     VehicleState = vs
}

A couple considerations:

As @Gert suggested, you should probably use foreign keys instead of whole object references.
I haven't considered the possibilities of null values in the example, you can take them into account using left joins with DefaultIfEmpty.

All the tables are related through foreign keys. Can you give an example of a simple combination query? — Brannon, Jun 16 '14 at 17:00

score 1 · Answer 2 · answered Jun 16 '14 at 20:49

In stead of setting object references you should set primitive foreign key values. From an object-oriented point of view this sounds like a heresy, but it's Entity Framework's recommended approach when it comes to setting associations efficiently.

Of course, there should be foreign key values to be set in the first place. In your VehicleStateLogEntryDbo this could look like:

public int VehicleIdentifier { get; set; } // or guid?
[ForeignKey("VehicleIdentifier")]
public VehicleDbo Vehicle { get; set }

The ForeignKey attribute tells EF that both properties belong together in a foreign key association. This can also be configured by the fluent API, e.g. in the OnModelCreating override:

modelbuilder.Entry<VehicleStateLogEntryDbo>()
            .HasRequired(v => v.Vehicle)
            .WithMany()
            .HasForeignKey(v => v.VehicleIdentifier);

By the way, having a Vehicle property only is referred to as an independent association.

So when you have these foreign key associations in place you can simply set the FK values. Maybe you should modify your DTO to transfer these values in stead of names etc.

is this EF6 code supposed to have queries in it?

2 Answers2