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

Question

我有一个查询目前太慢了。我正在尝试在主页上搜索一个代码(一个字符串)，它将为用户带来相关信息。例如。用户可以从主页搜索代码，这将在 Job、Work Phase、Wbs、Work Element、EA、Jobcard 和 Estimate 中搜索代码并返回相关信息。当我相信只需一次就可以完成时，我多次访问数据库以收集我需要的数据。我有许多链接的表:契约(Contract)、工作、工作阶段、Wbss、工程事件、工作卡和估算。契约(Contract)有一份工作 list ，工作有一个工作阶段列表，工作阶段有一个 Wbss 等的列表

有没有更快的方法来做到这一点？

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

免责声明:我是项目的所有者Entity Framework Plus

此库具有 Query Future 功能，允许在单次往返中批处理多个查询。

示例:

// 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();

维基:EF+ Query Future