Conditional joins with Linq

Question

Is there a way to incrementally / conditionally add joins to a query? I create a custom reporting tool for the client, and the client is provided with a list of objects that he can select for the request. The request will always use the base object ("FWOBid").

So, for example, if the client selects the objects "FWOBid", "FWOItem" and "FWOSellingOption", I would like 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 that the client can select about 6 objects that are all connected to each other, which leads to many different combinations of connections. I would like to avoid hard coding if possible.

Answer 1

- .

TSQL - , , , . .

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

Linq , . , - ( ):

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

. , .

, , , . , . - , ? " !". , . , , , , .

Answer 3

. , " ", :

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

, , . , . .

, , , , Id, Name, Text B C ( - , TextB TextC). :

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

, :

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

Answer 4

, .

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

        }
    }
}