Entity Framework inner join

Question

I have two tables something like this:

Customers:

Email
username

Products:

Username
Product

I'd like to fill a class with username, product, and email.

This is what I have so

var res = from H in db.Products
          join C in db.customers on H.Username equals C.Username
          where C.Username == H.Username
          select H;

Results results = res.Single();

However the catch is that I'm not sure exactly how this works, can anyone break it down for me?

What exactly is what you are unsure about? The .Single() or what results the query itself will give you,...? — Thomas, May 04 '15 at 08:14
As its an inner join it would give you only results that have values in both H and C (the where shouldn't be necessary as the join already has the appropriate value) if you are unsure about the syntax for an inner join: http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql — Thomas, May 04 '15 at 08:16
@Thomas I'm wondering what values will I get back... for example will this populat the results class with Username Email and Product? — Johnny Grimes, May 04 '15 at 08:18
Updated my answer to also include why a step like a dto / anonymous type is necessary — Thomas, May 04 '15 at 08:33

Thomas · Accepted Answer · 2015-05-04T08:31:56.633

  var res = from H in db.Products
                      join C in db.customers on H.Username equals C.Username
                      where C.Username == H.Username
                      select H;

            Results results = res.Single();

The code above makes an inner join between C and H (the where clause is not necessary) and results in all elements given back where an appropriately connected products and customers entry exist.

The select H "just" gives back all the data from the products. As you stated that you want a mix you need to do that differently.

I myself would be using an anonymous type (or a dto object). For an anonymous type:

select new { Username = H.Username, Product = H.Product, EMail = C.EMail}

Put together:

  var res = from H in db.Products
                      join C in db.customers on H.Username equals C.Username
                      where C.Username == H.Username
                      select new { Username = H.Username, Product = H.Product, EMail = C.EMail}

            Results results = res.Single();

For a DTO (Data Transfer Object) you would create a class with public set/get properties and use select new myClass(){....} instead of the select new {}.

DTOs are better in terms of reuse and that you have less problems with writing the names wrong that you use, ....

Just simply using select H, C sadly can't work as Linq wants to return just single objects per row (and additionally always the same datatype). Thus you need to put a container around the objects you want to return. In this case either anonymous types or DTOs.

Of course, the container can be as simple as `new { Product = H, Customer = C }`, pretty easy to work with. — Luaan, May 04 '15 at 08:51

Entity Framework inner join

1 Answers1