Conditional Joins With Linq

Question

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").

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

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.

Answer 1

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

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

Answer 2

In the Linq query syntax this is not possible, or looking at the other answers hardly readable. Not much more readable but another possibility would be to use the extension methods (sort of pseudo code):

        bool condition1;
        bool condition2;

        List<Bid> bids = new List<Bid>();
        List<SellingOption> sellingOptions = new List<SellingOption>();
        List<Item> items = new List<Item>();

        var result = bids.Select(x => new {bid = x, sellingOption = (SellingOption)null, item = (Item)null});

        if (condition1)
            result = result.Join(
                sellingOptions,
                x => x.bid.Id,
                x => x.BidId,
                (x, sellingOption) => new { x.bid, sellingOption, item = (Item)null });

        if (condition2)
            result = result.Join(
                items,
                x => x.bid.Id,
                x => x.BidId,
                (x, item) => new { x.bid, x.sellingOption, item });

Just see this as a sort of a concept. It is essentially the same that Peter Duniho did.

The thing is, if you don't want to immediately join on all options if not necessary, then it won't look that nice. Perhaps you should try to join all now and don't worry about performance. Have you ever measured how slow or fast it might be? Think of it as "I don't need it now!". If performance is indeed a problem, then you can act on it. But if it is not, and you won't know if you never tried, then leave it as the six joins you mentioned.