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 => database.Posts.Where(x => x.ID == id),
meta => database.Post_Metas.Where(x => x.Post_ID == id),
(post, meta) => new { Post = post, Meta = meta });
I'm new to using LINQ, so I'm not sure if this query is correct.
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 => post.ID, // Select the primary key (the first part of the "on" clause in an sql "join" statement)
meta => meta.Post_ID, // Select the foreign key (the second part of the "on" clause)
(post, meta) => new { Post = post, Meta = meta }) // selection
.Where(postAndMeta => postAndMeta.Post.ID == id); // where statement
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 => post.Post_id,
meta => meta.Post_id,
(post, meta) => 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 => 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 "when you load a Post, also load all of its records associated with it via the foreign key represented by the 'Post_metas' property":
var dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<Post>(p => p.Post_metas);
var dataContext = new MyDataContext();
dataContext.LoadOptions = dataLoadOptions;
var post = Posts.Single(p => 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.
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 => post.ID,
meta => meta.Post_ID,
(post, meta) => new { Post = post, Meta = meta });
You can apply the where clause afterwards, not as part of the key selector.
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 => post.ID == id)
.Select(post => 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;}
}
I've done something like this;
var certificationClass = _db.INDIVIDUALLICENSEs
.Join(_db.INDLICENSECLAsses,
IL => IL.LICENSE_CLASS,
ILC => ILC.NAME,
(IL, ILC) => new { INDIVIDUALLICENSE = IL, INDLICENSECLAsse = ILC })
.Where(o =>
o.INDIVIDUALLICENSE.GLOBALENTITYID == "ABC" &&
o.INDIVIDUALLICENSE.LICENSE_TYPE == "ABC")
.Select(t => new
{
value = t.PSP_INDLICENSECLAsse.ID,
name = t.PSP_INDIVIDUALLICENSE.LICENSE_CLASS,
})
.OrderBy(x => x.name);
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 => post.postId, // Primary Key
meta => meat.postId, // Foreign Key
(post, meta) => new { Post = post, Meta = meta });
Equivalent SQL Query
Select * FROM Posts P
INNER JOIN Post_Metas pm ON pm.postId=p.postId
Query Syntax for LINQ Join
var productOrderQuery = from product in Product.Setup()//outer sequence
join order in OrderDetails.Setup()//inner sequence
on product.Id equals order.ProductId //key selector
select new//result selector
{
OrderId = order.Id,
ProductId = product.Id,
PurchaseDate = order.PurchaseDate,
ProductName = product.Name,
ProductPrice = product.Price
};
Method Syntax for LINQ Join
var productOrderMethod = Product.Setup().//outer sequence
Join(OrderDetails.Setup(), //inner sequence
product => product.Id//key selector
,order=> order.ProductId //key selector
,(product,order)=> //projection result
new
{
OrderId = order.Id,
ProductId = product.Id,
PurchaseDate = order.PurchaseDate,
ProductName = product.Name,
ProductPrice = product.Price
}
);
Product.cs for reference
class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public static IEnumerable<Product> Setup()
{
return new List<Product>()
{
new Product(){Id=1, Name="Bike", Price=30.33M },
new Product(){Id=2, Name="Car", Price=50.33M },
new Product(){Id=3, Name="Bus", Price=60.33M }
};
}
}
OrderDetails.cs class for reference
class OrderDetails
{
public int Id { get; set; }
public virtual int ProductId { get; set; }
public DateTime PurchaseDate { get; set; }
public static IEnumerable<OrderDetails> Setup()
{
return new List<OrderDetails>()
{
new OrderDetails(){Id=1, ProductId=1, PurchaseDate= DateTime.Now },
new OrderDetails(){Id=2, ProductId=1, PurchaseDate=DateTime.Now.AddDays(-1) },
new OrderDetails(){Id=3, ProductId=2, PurchaseDate=DateTime.Now.AddDays(-2) }
};
}
}
