Join Where with LINQ and Lambda

Question

I m having trouble with a query written in LINQ and Lambda  So far  I m getting a lot of errors here s my code   int id   1  var query   database Posts Join database Post Metas                                  post   gt  database Posts Where x   gt  x ID    id                                   meta   gt  database Post Metas Where x   gt  x Post ID    id                                    post  meta    gt  new   Post   post  Meta   meta       I m new to using LINQ  so I m not sure if this query is correct

User · Answer

This linq query Should work for you  It will get all the posts that have post meta    var query   database Posts Join database Post Metas                                  post   gt  post postId     Primary Key                                 meta   gt  meat postId     Foreign Key                                  post  meta    gt  new   Post   post  Meta   meta       Equivalent SQL Query   Select   FROM Posts P INNER JOIN Post Metas pm ON pm postId p postId

User · Answer

Posting because when I started LINQ   EntityFramework  I stared at these examples for a day   If you are using EntityFramework  and you have a navigation property named Meta on your Post model object set up  this is dirt easy  If you re using entity and don t have that navigation property  what are you waiting for   database    Posts    Where post   gt  post ID    id     Select post   gt  new   post  post Meta       If you re doing code first  you d set up the property thusly   class Post      Key    public int ID  get  set    public int MetaID   get  set      public virtual Meta Meta  get  set

User · Answer

Daniel has a good explanation of the syntax relationships  but I put this document together for my team in order to make it a little simpler for them to understand  Hope this helps someone

User · Answer

I find that if you re familiar with SQL syntax  using the LINQ query syntax is much clearer  more natural  and makes it easier to spot errors   var id   1  var query      from post in database Posts    join meta in database Post Metas on post ID equals meta Post ID    where post ID    id    select new   Post   post  Meta   meta      If you re really stuck on using lambdas though  your syntax is quite a bit off  Here s the same query  using the LINQ extension methods   var id   1  var query   database Posts       your starting point - table in the  from  statement     Join database Post Metas     the source table of the inner join       post   gt  post ID            Select the primary key  the first part of the  on  clause in an sql  join  statement        meta   gt  meta Post ID       Select the foreign key  the second part of the  on  clause         post  meta    gt  new   Post   post  Meta   meta       selection     Where postAndMeta   gt  postAndMeta Post ID    id         where statement

User · Answer

It could be something like  var myvar   from a in context MyEntity             join b in context MyEntity2 on a key equals b key             select new   prop1   a prop1  prop2  b prop1

User · Answer

1 equals 1 two different table join  var query   from post in database Posts             join meta in database Post Metas on 1 equals 1             where post ID    id             select new   Post   post  Meta   meta

User · Answer

You could go two ways with this  Using LINQPad  invaluable if you re new to LINQ  and a dummy database  I built the following queries  Posts Join      Post metas      post   gt  post Post id      meta   gt  meta Post id       post  meta    gt  new   Post   post  Meta   meta      or from p in Posts join pm in Post metas on p Post id equals pm Post id select new   Post   p  Meta   pm    In this particular case  I think the LINQ syntax is cleaner  I change between the two depending upon which is easiest to read   The thing I d like to point out though is that if you have appropriate foreign keys in your database   between post and post meta  then you probably don t need an explicit join unless you re trying to load a large number of records  Your example seems to indicate that you are trying to load a single post and its metadata  Assuming that there are many post meta records for each post  then you could do the following  var post   Posts Single p   gt  p ID    1   var metas   post Post metas ToList     If you want to avoid the n 1 problem  then you can explicitly tell LINQ to SQL to load all of the related items in one go  although this may be an advanced topic for when you re more familiar with L2S   The example below says  quot when you load a Post  also load all of its records associated with it via the foreign key represented by the  Post metas  property quot   var dataLoadOptions   new DataLoadOptions    dataLoadOptions LoadWith lt Post gt  p   gt  p Post metas    var dataContext   new MyDataContext    dataContext LoadOptions   dataLoadOptions   var post   Posts Single p   gt  p ID    1      Post metas loaded automagically  It is possible to make many LoadWith calls on a single set of DataLoadOptions for the same type  or many different types  If you do this lots though  you might just want to consider caching

User · Answer

I ve done something like this   var certificationClass    db INDIVIDUALLICENSEs      Join  db INDLICENSECLAsses          IL   gt  IL LICENSE CLASS          ILC   gt  ILC NAME           IL  ILC    gt  new   INDIVIDUALLICENSE   IL  INDLICENSECLAsse   ILC         Where o   gt           o INDIVIDUALLICENSE GLOBALENTITYID     ABC   amp  amp          o INDIVIDUALLICENSE LICENSE TYPE     ABC        Select t   gt  new                       value   t PSP INDLICENSECLAsse ID              name   t PSP INDIVIDUALLICENSE LICENSE CLASS                                  OrderBy x   gt  x name

User · Answer

Your key selectors are incorrect  They should take an object of the type of the table in question and return the key to use in the join  I think you mean this   var query   database Posts Join database Post Metas                                  post   gt  post ID                                  meta   gt  meta Post ID                                   post  meta    gt  new   Post   post  Meta   meta       You can apply the where clause afterwards  not as part of the key selector

[c#] Join/Where with LINQ and Lambda

Examples related to c#

Examples related to linq

Examples related to join

Examples related to lambda