Help to: Convert a sql command to EF 4.1 Code First
I have this schema:
...and I want to select some records using this query:
SELECT
Articles.ArticleId,
Articles.Title,
Articles.Tooltip,
Articles.UriTitle,
Articles.SubTitle,
Articles.Published,
Tags.TagId
FROM Articles INNER JOIN
ArticleTag ON Articles.ArticleId = ArticleTag.ArticleId INNER JOIN
Tags ON ArticleTag.TagId = Tags.TagId
WHERE (Tags.TagId = @tagid) AND (Articles.Published = 1)
Can anybody help me to convert this query to EF 4.1 Code First (a lambda expression) please?
UPDATE:
I use @Ladislav Mrnka code (as a lambda expression) and code works! Now I put generated sql here, and check it please to ensure there is no problem:
// C#:
var query = context.Articles.Where(a => a.Published && a.Tags.Any(t => t.TagId == tagId))
.Select(a = new {
a.ArticleId,
a.Title,
a.Tooltip,
a.UriTitle,
a.SubTitle,
a.Published
});
and the generated sql is
SELECT
[Extent1].[ArticleId] AS [ArticleId],
[Extent1].[Title] AS [Title],
[Extent1].[Tooltip] AS [Tooltip],
[Extent1].[UriTitle] AS [UriTitle],
[Extent1].[SubTitle] AS [SubTitle],
[Extent1].[Description] AS [Description],
[Extent1].[Keywords] AS [Keywords],
[Extent1].[MetaDescription] AS [MetaDescription],
[Extent1].[CreatedAt] AS [CreatedAt],
[Extent1].[UpdatedAt] AS [UpdatedAt],
[Extent1].[Viewed] AS [Viewed],
[Extent1].[Published] AS [Published],
[Extent1].[Body] AS [Body]
FROM [dbo].[Articles] AS [Extent1]
WHERE ([Extent1].[Published] = 1) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[ArticleTag] AS [Extent2]
WHER开发者_如何学CE ([Extent1].[ArticleId] = [Extent2].[ArticleId]) AND ([Extent2].[TagId] = @p__linq__0)
))
Try:
int tagId = ...;
var query = from a in context.Articles
where a.Tags.Any(t => t.TagId == tagId) && a.Published
select new
{
a.ArticleId,
a.Title,
a.Tooltip,
a.UriTitle,
a.SubTitle,
a.Published,
tagId // I'm not sure if this will work but let give it a try
};
If selecting TagId
doesn't work it is not too big problem because you know that all records have same tag Id.
精彩评论