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

I hope this is an improvement over previous answers.

public class Bids
{
    public int Id { get; set; }
    public double Price { get; set; }
}

public class BidSection
{
    public int BidId { get; set; }
}

public class SellingOptions
{
    public int BidId { get; set; }
    public int Quantity { get; set; }
}

public class Item
{
    public int ItemId { get; set; }
    public BidSection FWOBidSection { get; set; }
}

public class ConditionalJoin
{
    public bool jOpt1 { get; set; }
    public bool jOpt2 { get; set; }

    public ConditionalJoin(bool _joinOption1, bool _joinOption2)
    {
        jOpt1 = _joinOption1;
        jOpt2 = _joinOption2;
    }

    public class FBandSo
    {
        public Bids FWOBids { get; set; }
        public SellingOptions FWOSellingOptions { get; set; }
    }

    public class FBandI
    {
        public Bids FWOBids { get; set; }
        public Item FWOItem { get; set; }
    }

    public void Run()
    {
        var fwoBids = new List<Bids>();
        var sellingOptions = new List<SellingOptions>();
        var fwoItems = new List<Item>();

        fwoBids.Add(new Bids() { Id = 1, Price = 1.5 });
        sellingOptions.Add(new SellingOptions() { BidId = 1, Quantity = 2 });
        fwoItems.Add(new Item() { ItemId = 10, FWOBidSection = new BidSection() { BidId = 1 } });

        IQueryable<Bids> fb = fwoBids.AsQueryable();
        IQueryable<SellingOptions> so = sellingOptions.AsQueryable();
        IQueryable<Item> i = fwoItems.AsQueryable();

        IQueryable<FBandSo> FBandSo = null;
        IQueryable<FBandI> FBandI = null;

        if (jOpt1)
        {
            FBandSo = from f in fb
                      join s in so on f.Id equals s.BidId
                      select new FBandSo()
                      {
                          FWOBids = f,
                          FWOSellingOptions = s
                      };
        }

        if (jOpt2)
        {
            FBandI = from f in fb
                     join y in i on f.Id equals y.FWOBidSection.BidId
                     select new FBandI()
                     {
                         FWOBids = f,
                         FWOItem = y
                     };
        }

        if (jOpt1 && jOpt2)
        {
            var query = from j1 in FBandSo
                        join j2 in FBandI
                        on j1.FWOBids.Id equals j2.FWOItem.FWOBidSection.BidId
                        select new
                        {
                            FWOBids = j1.FWOBids,
                            FWOSellingOptions = j1.FWOSellingOptions,
                            FWOItems = j2.FWOItem
                        };

        }
    }
}

Answer 3

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.

Answer 4

It's hard to provide a really good example solution without a really good example problem. However, what I mean by "chain the queries" is something like this:

var query = from x in dba select new { A = x, B = (B)null, C = (C)null };

if ((joinType & JoinType.B) != 0)
{
    query = from x in query
            join y in dbb on x.A.Id equals y.Id
            select new { A = x.A, B = y, C = x.C };
}

if ((joinType & JoinType.C) != 0)
{
    query = from x in query
            join y in dbc on x.A.Id equals y.Id
            select new { A = x.A, B = x.B, C = y };
}

That is, based on the appropriate condition, query the previous result with another join. Note that to do this successfully, each query must produce the same type. Otherwise, it's not possible to assign a new query to the previous query result variable.

Note that while in the above, I simply have a separate property for each possible input type, I could have instead had the type simply have properties for the input columns, Id, Name, and then the Text properties from the B and C types (which would have to be named differently in the query result type, e.g. TextB and TextC). That would look like this:

var query = from x in dba select new { Id = x.Id, Name = x.Name,
    TextB = (string)null, TextC = (string)null };

if ((joinType & JoinType.B) != 0)
{
    query = from x in query
            join y in dbb on x.Id equals y.Id
            select new { Id = x.Id, Name = x.Name, TextB = y.Text, TextC = x.TextC };
}

if ((joinType & JoinType.C) != 0)
{
    query = from x in query
            join y in dbc on x.Id equals y.Id
            select new { Id = x.Id, Name = x.Name, TextB = x.TextB, TextC = y.Text };
}

Here is a complete code example that includes the above logic in a runnable program:

class A
{
    public string Name { get; private set; }
    public int Id { get; private set; }

    public A(string name, int id)
    {
        Name = name;
        Id = id;
    }

    public override string ToString()
    {
        return "{" + Name + ", " + Id + "}";
    }
}

class B
{
    public int Id { get; private set; }
    public string Text { get; private set; }

    public B(int id, string text)
    {
        Id = id;
        Text = text;
    }

    public override string ToString()
    {
        return "{" + Id + ", " + Text + "}";
    }
}

class C
{
    public int Id { get; private set; }
    public string Text { get; private set; }

    public C(int id, string text)
    {
        Id = id;
        Text = text;
    }

    public override string ToString()
    {
        return "{" + Id + ", " + Text + "}";
    }
}

[Flags]
enum JoinType
{
    None = 0,
    B = 1,
    C = 2,
    BC = 3
}

class Program
{
    static void Main(string[] args)
    {
        A[] dba =
        {
            new A("A1", 1),
            new A("A2", 2),
            new A("A3", 3)
        };
        B[] dbb =
        {
            new B(1, "B1"),
            new B(2, "B2"),
            new B(3, "B3")
        };
        C[] dbc =
        {
            new C(1, "C1"),
            new C(2, "C2"),
            new C(3, "C3")
        };

        JoinType joinType;

        while ((joinType = _PromptJoinType()) != JoinType.None)
        {
            var query = from x in dba select new { A = x, B = (B)null, C = (C)null };

            if ((joinType & JoinType.B) != 0)
            {
                query = from x in query
                        join y in dbb on x.A.Id equals y.Id
                        select new { A = x.A, B = y, C = x.C };
            }

            if ((joinType & JoinType.C) != 0)
            {
                query = from x in query
                        join y in dbc on x.A.Id equals y.Id
                        select new { A = x.A, B = x.B, C = y };
            }

            foreach (var item in query)
            {
                Console.WriteLine(item);
            }
            Console.WriteLine();
        }
    }

    private static JoinType _PromptJoinType()
    {
        JoinType? joinType = null;

        do
        {
            Console.Write("Join type ['A' for all, 'B', 'C', or 'N' for none]");
            ConsoleKeyInfo key = Console.ReadKey();
            Console.WriteLine();

            switch (key.Key)
            {
            case ConsoleKey.A:
                joinType = JoinType.BC;
                break;
            case ConsoleKey.B:
                joinType = JoinType.B;
                break;
            case ConsoleKey.C:
                joinType = JoinType.C;
                break;
            case ConsoleKey.N:
                joinType = JoinType.None;
                break;
            default:
                break;
            }
        } while (joinType == null);

        return joinType.Value;
    }
}