实体框架:如何单次查询数据库中的许多相关表 [英] Entity Framework: How to query a number of related tables in a database making a single trip

问题描述

我有一个查询，该查询当前太慢了. 我正在尝试在主页上搜索代码(字符串)，以便为用户提供相关信息. 例如.用户可以从主页上搜索代码，这将在工作"，工作阶段"，工作地点"，工作元素"，"EA"，工作卡"和估计"中搜索代码，并返回相关信息. 当我相信可以在一个数据库中完成一次数据访问时，我会进行多次数据库访问以收集所需的数据. 我有一些都链接在一起的表: 合同，工作，工作阶段，工作，工程活动，工作卡和估算. 合同中有一份工作清单， 作业有工作阶段列表， 工作阶段列出了Wbss等

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

有更快的方法吗?

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;
}

推荐答案

是的，最肯定的是有一种查询多个表的方法.您可以对查询使用方法扩展.例如:

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.

您也可以使用UnionAll()方法，但是您必须为此单独定义查询

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

这篇关于实体框架:如何单次查询数据库中的许多相关表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！