Linq. Select from multiple tables

Question

In project I have this tables:

1. Product(id,catalogId, manufacturerId...)
2. Catalog
3. Manufacturer

Also Product model (id, name, catalogId, catalogTitle, manufacturerId, manufacturerName).

How can write in Linq this SQL query below if I want get Product item?

SELECT Product.Name, Product.CatalogId, Product.ManufacturerId, [Catalog].Name, Manufacturer.Name
FROM Product, [Catalog], Manufacturer
WHERE [Catalog].Id=Product.CatalogId AND Manufacturer.id=Product.ManufacturerId AND Product.Active=1

Answer 1

First, I'll answer your question.. then address your answer to comments. To answer your question, in Linq you would do the following:

from p in Product
join c in Catalog on c.Id equals p.CatalogId
join m in Manufacturer on m.Id equals p.ManufacturerId
where p.Active == 1
select new { Name = p.Name, CatalogId = p.CatalogId, ManufacturerId = p.ManufacturerId, CatalogName = c.Name, ManufacturerName = m.Name };

This will give you an anonymous object with the items you requested. If you need to use this elsewhere (and you're not using dynamic objects), I would suggest creating a view-model, and instantiating one of those in your select.

Example:

public class ProductInfoView 
{
     public string Name { get; set; }
     public int CatalogId { get; set; }
     public int ManufacturerId { get; set; }
     public string CatalogName { get; set; }
     public string ManufacturerName { get; set; }
}


from p in Product
join c in Catalog on c.Id equals p.CatalogId
join m in Manufacturer on m.Id equals p.ManufacturerId
where p.Active == 1
select new ProductInfoView() { Name = p.Name, CatalogId = p.CatalogId, ManufacturerId = p.ManufacturerId, CatalogName = c.Name, ManufacturerName = m.Name };

This will make referencing your query results a little less painful.

To answer your comment, you're doing a lot of joins if all you want is the product. Your criteria will only ensure three things

1. Your product's Active flag is 1
2. Your product has an existing Catalog entry
3. Your product has an existing Manufacturer entry

If #2 and #3 are superfluous and you don't necessarily need the names, you could simply do:

from p in Product
where p.Active == 1
select p

If Product is a CRUD model, you could potentially deep-load it to include Manufacturer/Catalog information, or use the aforementioned view-model.

Good luck!

Answer 2

To combine results from multiple tables without explicitly joins:

from p in Product
from c in Catalog
from m in Manufacturer
where c.Id == p.CatalogId && m.Id == p.ManufacturerId && p.Active == 1
select new 
    { 
        p.Name,
        p.CatalogId,
        p.ManufacturerId,
        c.Name,
        m.Name 
    };