How do I write Joins between multiple tables with Linq and ASP.Net Forms

Question

I've implemented a solution that works, but LINQ is a something nre for me and I am struggling to find the required information.

Here is what I have:

var prodCat = from pc in db.ProductCategories
              where pc.Category == Int32.Parse(CategoriesDropper.SelectedValue)
              select pc;

List<int> productIds = new List<int>();
foreach (var pc in prodCat)
{
    productIds.Add(pc.Product);
}

var products = from p in db.Products
               where productIds.Contains(p.Id)
               select p;

ProductsGridView.DataSource = products;
ProductsGridView.DataBind();

Ideally I would like to achieve the same result with a single select rather than two.

Aaron Hawkins · Accepted Answer · 2013-02-05 20:00:49Z

2

var products = from pc in db.ProductCategories
               join p in db.Products on pc.Product equals p.Id
               where pc.Category == Int32.Parse(CategoriesDropper.SelectedValue)
               select p;

 ProductsGridView.DataSource = products;
 ProductsGridView.DataBind();

See LINQ - Join Operators in C# for Visual Studio 2010 for reference.

Hope this helps!

answered Feb 5, 2013 at 20:00

Aaron Hawkins

2,7111 gold badge20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Wooley · Accepted Answer · 2013-02-05 20:01:37Z

1

You don't mention which flavor of LINQ you are using, but assuming you have the associations set between ProductCategories and Products, you should be able to do something like this:

int Cat = Int32.Parse(CategoriesDropper.SelectedValue);
var products = from pc in db.ProductCategories
               where pc.Category == cat
               from p in pc.Products
               select p;

answered Feb 5, 2013 at 20:01

Jim Wooley

10.4k1 gold badge27 silver badges44 bronze badges

3 Comments

DrLazer Over a year ago

does this end up as a single select or will it execute two selects from the sql db?

Jim Wooley Over a year ago

Because of the composable nature and deferred execution, it will be one query which is generated when .GetEnumerator is called in the data binding.

Jim Wooley Over a year ago

Join is important for associating values that aren't naturally associated as part of an object graph. I often find join abused in LINQ to do object graph traversal because people think in SQL rather than OOP when working with data. Typically, both syntaxes achieve the same resulting SQL

Dustin Kingen · Accepted Answer · 2013-02-05 20:06:52Z

1

var selectedCategory = Int32.Parse(CategoriesDropper.SelectedValue); //Parse the value once
var products = 
    from p in db.Products //foreach product in the database
    join pc in db.ProductCategories
    on pc.Product equals p.Id //where the Ids match
    where pc.Category == selectedCategory  //and with the same category
    select p; //select the product
ProductsGridView.DataSource = products;
ProductsGridView.DataBind();

answered Feb 5, 2013 at 20:06

Dustin Kingen

21.4k7 gold badges55 silver badges94 bronze badges

Collectives™ on Stack Overflow

How do I write Joins between multiple tables with Linq and ASP.Net Forms

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related