Entity Framework: How to query a number of related tables in a database making a single trip

Question

I have a query that is currently far too slow. I am trying to search a Code (a string) on the main page that will bring the user the relevant info. Eg. The user can search a code from the main page and this will search for the code in Job, Work Phase, Wbs, Work Element, EA, Jobcard and Estimate and return the relevant info. I make a number of trips to the database to collect the data i need when I believe it can be done in just one. I have a number of tables that are all linked: Contracts, Jobs, WorkPhases, Wbss, Engineering Activities, Jobcards and Estimates. Contracts have a list of Jobs, Jobs have a list of Workphases, Workphases have a list of Wbss etc

Is there a quicker way to do this?

public Result Handle(Query query)
{                   
    query.Code = query.Code ?? string.Empty;

    var result = new Result();

    //result.SetParametersFromPagedQuery(query);
    result.Items = new List<Item>();

    if (query.SearchPerformed)
    {
        var contracts = _db.Contracts.AsEnumerable().Where(x => x.Code == query.Code);

        result.Items = result.Items.Concat(contracts.Select(x => new Item()
        {
            Code = x.Code,
            Id = x.Id,
            Name = x.Name,
            Type = MainPageSearchEnum.Contract,
            ContractName = x.Name,
            Url = string.Format("Admin/Contract/Edit/{0}", x.Id)
        })).ToList();


        var jobs = _db.Jobs.AsEnumerable().Where(x => x.Code == query.Code);

        result.Items = result.Items.Concat(jobs.Select(x => new Item()
        {
            Code = x.Code,
            Id = x.Id,
            Name = x.Name,
            ContractName = x.Contract.Name,
            Type = MainPageSearchEnum.Job,
            Url = string.Format("Admin/Job/Edit/{0}", x.Id)
        })).ToList();

        //var workPhases = _db.WorkPhases.AsEnumerable().Where(x => x.ContractPhase.Code.ToLower() == query.Code.ToLower());
        var workPhases = _db.WorkPhases.AsEnumerable().Where(x => x.ContractPhase.Code == query.Code);

        result.Items = result.Items.Concat(workPhases.Select(x => new Item()
        {
            Code = x.ContractPhase.Code,
            Id = x.Id,
            Name = x.ContractPhase.Name,
            Type = MainPageSearchEnum.WorkPhase,
            Url = string.Format("Admin/WorkPhase/Edit/{0}", x.Id)
        })).ToList();

        var wbss = _db.WBSs.AsEnumerable().Where(x => x.Code == query.Code);

        result.Items = result.Items.Concat(wbss.Select(x => new Item()
        {
            Code = x.Code,
            Id = x.Id,
            Name = x.Name,
            Type = MainPageSearchEnum.WBS,
            Url = string.Format("Admin/WBS/Edit/{0}", x.Id)
        })).ToList();

        var eas = _db.EngineeringActivities.AsEnumerable().Where(x => x.Code == query.Code);

        result.Items = result.Items.Concat(eas.Select(x => new Item()
        {
            Code = x.Code,
            Id = x.Id,
            Name = x.Name,
            Type = MainPageSearchEnum.EA,
            Url = string.Format("Admin/EngineeringActivity/Edit/{0}", x.Id)
        })).ToList();

        var jcs = _db.Jobcards.AsEnumerable().Where(x => x.Code == query.Code);

        result.Items = result.Items.Concat(jcs.Select(x => new Item()
        {
            Code = x.Code,
            Id = x.Id,
            Name = x.Name,
            Type = MainPageSearchEnum.EA,
            Url = string.Format("Admin/JobCard/Edit/{0}", x.Id)
        })).ToList();

        var estimates = _db.Estimates.AsEnumerable().Where(x => x.Code == query.Code);

        result.Items = result.Items.Concat(estimates.Select(x => new Item()
        {
            Code = x.Code,
            Id = x.Id,
            Name = x.Name,
            Type = MainPageSearchEnum.Estimate,
            Url = string.Format("Estimation/Estimate/Edit/{0}", x.Id)
        })).ToList();

    }

    return result;
}

Answer 1

Disclaimer: I'm the owner of the project Entity Framework Plus

This library has a Query Future feature which allows batching multiple queries in a single roundtrip.

Example:

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();

// CREATE a pending list of future queries
var futureCountries = ctx.Countries.Where(x => x.IsActive).Future();
var futureStates = ctx.States.Where(x => x.IsActive).Future();

// TRIGGER all pending queries in one database round trip
// SELECT * FROM Country WHERE IsActive = true;
// SELECT * FROM State WHERE IsActive = true
var countries = futureCountries.ToList();

// futureStates is already resolved and contains the result
var states = futureStates.ToList();

Wiki: EF+ Query Future

Answer 2

Yes there most certainly is a way to query multiple tables. You can use the Include() method extension for your query. for instance:

var examplelist = _db.Contracts.(v => v.id == "someid" && v.name == "anotherfilter").Include("theOtherTablesName").ToList();

You can include as many tables as you like this way. This is the recommended method.

You can also use the UnionAll() method but you'd have to define your queries separately for this

Answer 3

Have you tried the Union / UnionAll operator?

It's purpose is exactly like you wish - combine the identical data from different sources.

Furthermore due to the concept of deferred execution your query will only be executed when you actually iterate over the result (or call a method that does that, for example - .ToList()

var contractsQuery = _db.Contracts.AsEnumerable().Where(x => x.Code == query.Code).Select(x=>new {Code=x.Code, Id=x.Id, ...});
var jobsQuery = _db.Jobs.AsEnumerable().Where(x => x.Code == query.Code).Select(x=>new{Code=x.Code, Id=x.Id, ...});
var workPhasesQuery = _db.WorkPhases.AsEnumerable().Where(x => x.ContractPhase.Code == query.Code).Select(x=>new{Code=x.Code, Id=x.Id, ...});
    // and so on
var combinedQuery = contractsQuery.UnionAll(jobsQuery).UnionAll(workPhasesQuery ).UnionAll(...
var result = combinedQuery.ToList();

A similar question is Union in linq entity framework
Another code sample can be found here

Please notice that this is exactly the same concept of manipulating data as in T-SQL union, and under the covers you will get an sql query using a union operator