有条件加入Linq [英] Conditional Joins With Linq

问题描述

有没有一种方法可以逐步/有条件地将联接添加到查询中?我正在为客户端创建自定义报告工具，并且向客户端提供了他/她可以选择查询的对象的列表.查询中将始终有一个基础对象("FWOBid").

Is there a way to progressively / conditionally add joins to a query? I am creating a custom reporting tool for a client, and the client is given a list of objects he/she can select to query on. There will always be a base object used in the query ("FWOBid").

例如，如果客户选择对象"FWOBid"，"FWOItem"和"FWOSellingOption"，我想这样做:

So, for example, if the customer selects objects "FWOBid", "FWOItem", and "FWOSellingOption", I'd want to do this:

var query = from fb in fwoBids

// if "FWOSellingOption", add this join
join so in sellingOptions on fb.Id equals so.BidId

// if "FWOItem", add this join
join i in fwoItems on fb.Id equals i.FWOBidSection.BidId

// select "FWOBid", "FWOItem", and "FWOSellingOption" (everything user has selected)
select new { FWOBid = fb, FWOSellingOption = so, FWOItem = i };

诀窍是，客户可以选择大约6个彼此相关的对象，从而产生许多不同的联接组合.如果可能的话，我想避免对它们进行硬编码.

The trick is the customer can select about 6 objects that are all related to each other, resulting in many different combinations of joins. I'd like to avoid hard coding those if possible.

推荐答案

一种选择是结合左联接进行一些自定义联接.

One option is to do some custom join combined with left joins.

一个好的TSQL后端始终使用所有联接在性能上不会有任何缺点，因为如果条件始终为false，则优化器只会删除该联接.但这应该签出.

A decent TSQL backend should not get any drawbacks in terms of performance for always using all the joins, since the optimers would just remove the join if the condition is always false. But this should be checked out.

bool joinA = true;
bool joinB = false;
bool joinC = true;

var query = from fb in fwoBids
            join so in sellingOptions on new { fb.Id, Select = true } equals new { Id = so.BidId, Select = joinA } into js
            from so in js.DefaultIfEmpty()
            join i in fwoItems on new { fb.Id, Select = true } equals new { Id = i.FWOBidSection.BidId, Select = joinB } into ji
            from i in ji.DefaultIfEmpty()
            join c in itemsC on new { fb.Id, Select = true } equals new { Id = c.BidId, Select = joinC }
            select new
            {
                FWOBid = fb,
                FWOSellingOption = so,
                FWOItem = i,
                ItemC = c
            };            

这篇关于有条件加入Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！