CODEWITHSUNDEEP
c#linq
DarkW1nter
DarkW1nter

Reputation: 2851

WHERE clauses in multi-table LINQ query

I'm looking for suggestions on how to filter the LINQ query below. You can see that there are 6 tables involved, an that the table being selected from is b - booking. Then, depending on whether the passed-in variables are null or not, I add WHERE clauses, but as I understand it if I select from b then I can only add the WHERE clause to b, so if I want to add a WHERE to any of the other joined tables how could I do this in LINQ?

public List<DAL.Booking> GetBookingMain(string _booking, string _project, string _customer, string _location,
                                            string _supplierSKU, string _eisSKU, string _assetREF)
    {
        List<DAL.Booking> list_item = new List<DAL.Booking>();
        var qry = (from b in edc.Bookings 
        join cu in edc.Customers on b.customer_ref equals cu.customer_ref
        join loc in edc.Locations on cu.customer_ref equals loc.customer_ref
        join pl in edc.Pallets on i.booking_ref equals pl.booking_id
        join pp in edc.ProductsToPallets on pl.PalletID equals pp.palletID
        join pr in edc.Products on pp.productID equals pr.product_id
        select b);
        if (_booking != Null)
        {
            qry = qry.Where(b => b.booking_ref == _booking);
        }
        if (_project != Null)
        {
            qry = qry.Where(b => b.project_ref == _project);
        }
        if (_customer != Null)
        {
            qry = qry.Where(b => b.customer_ref == _customer);
        }
        if (_location != Null)
        {
            //add WHERE for table loc
        }
        if (_supplierSKU != Null)
        {
            //add WHERE for table pr
        }
        if (_eisSKU != Null)
        {
            //add WHERE for table pr
        }
        if (_assetREF != Null)
        {
            //add WHERE for table pp
        }
        list_item = qry.ToList();
        return list_item;
    }

Thanks

Upvotes: 0

Views: 361

Answers (1)

Kirill Bestemyanov
Kirill Bestemyanov

Reputation: 11964

You can do this:

var bQuery = edc.Bookings;
var quQuery = edc.Customers;
var locQuery = edc.Locations;
...
var prQuery = edc.Products;

    if (_booking != Null)
    {
        bQuery = bQuery.Where(i => i.booking_ref == _booking);
    }
    if (_project != Null)
    {
        prQuery = prQuery.Where(i => i.project_ref == _project);
    }
    ...
    var list_item = (from b in bQuery 
    join cu in cuQuery on b.customer_ref equals cu.customer_ref
    join loc in locQuery on cu.customer_ref equals loc.customer_ref
    ...
    join pr in prQuery.Products on pp.productID equals pr.product_id
    select b).ToList();

We form queries for all joined tables, but not execute its. Then we add filter expressions, then we form final query by joining all queries formed before and materialize final query to list.

Upvotes: 2

Related Questions