CODEWITHSUNDEEP
c#entity-frameworklinq
Arjun K P
Arjun K P

Reputation: 25

How do i combine 2 Linq queries into 1?

Parameter - (String Supplier)

var supplierID = db.SupplierTable
            .Where(m => m.SupplierName.Trim().ToLower() == Supplier.Trim().ToLower())
            .Select(m => m.SupplierID).First();

var supplierREFs = db.ProductSuppliers
            .Where(m => m.SupplierID == supplierID)
            .Select(m => m.SupplierRef);

can the above 2 queries be combined into 1 query?

Upvotes: 1

Views: 612

Answers (3)

Harald Coppoolse
Harald Coppoolse

Reputation: 30512

Apparently there is a one-to-many relationship between Supplier items from your SupplierTable and ProductSupplier items from ProductSuppliersTable:

Every Supplier has zero or more ProductSuppliers, and every ProductSupplier belongs to exactly one Supplier.

If you've configured your one-to-many relationship according to the default entity framework conventions you'll have classes similar to the following:

class Supplier
{
    public int Id {get; set;}
    // Every Supplier has zero or more ProductSupplierTables:
    public virtual ICollection<ProductSuppierTable> ProductSupplierTables {get; set;}

    public string SupplierName {get; set;}
    ...
}

class ProductSupplier
{
    public int Id {get; set;}
    // every ProductSupplier belongs to exactly one Supplier using foreign key
    public virtual Supplier Supplier {get; set;}
    public int SupplierId {get; set;}

    public string SupplierRef {get; set;}
    ...
}

It might be that some identifier names differ, but the Idea is clear: your Supplier needs a virtual ICollection of ProductSuppliers, and your ProductSupplier needs a foreign key to the Supplier as well as a virtual Supplier.

Once you've done this correctly, entity framework knows the primary keys and the foreign keys and create the correct tables for you.

The nice thing is that your DbContext and your DbSets know about this one-to-many relationship. Whenever you need Suppliers with their ProductSuppliers, you don't need to perform a join on foreign key. You can use the ICollection instead.

Rephrase for yourself what you want.

I have the name of a supplier and I want the first Supplier items from my SupplierTable that has a Trimmed LowerCase name that equals this name, together with all the SupplierRefs from the supplier's ProductSuppliers.

using (var dbContext = new MyDbContext())
{
    string supplierName = Supplier.Trim().ToLower()
    dbContext.Suppliers
       .Where(supplier => String.ToLower(supplier.SupplierName) == supplierName)
       .Select(supplier => new
       {   // to improve performance: select only the properties you'll use
           SupplierId = supplier.Id,
           ...

           // select also all the SupplierRefs from its ProductSuppliers:
           SupplierRefs = supplier.ProductSupplier
               .Select(productSupplier => productSupplier.SupplierRef)
               .ToList(),
        })
        // I only want the First (or default) of these suppliers:
        .FirstOrDefault();
    }
}

Entity framework knows about your one-to-many, and which foreign key is used for this. Entity Framework will do the proper join and select for you.

Upvotes: 0

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

You can make it joining two entities like; 

Supplier = Supplier.Trim().ToLower();

var supplierREFs = (from st in db.SupplierTable
                                join sr in supplierREFs on st.SupplierID equals sr.SupplierID
                                where 
                                st.SupplierName.Trim().ToLower() == Supplier
                                select sr)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

Sure, you need a sub-query:

Supplier = Supplier.Trim();  // do this once and not in the query

var supplierREFs = db.ProductSuppliers
    .Where(m => db.SupplierTable
        .Where(s => m.SupplierID == s.SupplierID)
        .Any(s => String.Equals(s.SupplierName.Trim(), Supplier, StringComparison.CurrentCultureIgnoreCase))
     )
    .Select(m => m.SupplierRef);

This query takes every ProductSupplier which SupplierName matches the given Supplier(ignoring the case). Then it returns it's SupplierRef.

Side-note: if you don't store SupplierNames with leading/trailing spaces in the database you should not compare with s.SupplierName.Trim() but s.SupplierName.

Upvotes: 2

Related Questions