Question
I have this simple Blog database structure with 4 tables:
and some sample data in each table looks like this:
Blogs table:
Posts table:
Tags table:
PostTags table:
And I have this SQL script.
SELECT b.Id,
b.Title,
p.Id,
p.Title,
p.PostContent,
t.Name
FROM dbo.Blogs b
JOIN Posts p ON p.BlogId = b.Id
LEFT JOIN PostTags pt ON pt.PostId = p.Id
LEFT JOIN Tags t ON t.Id = pt.TagId
WHERE b.Id = 1
AND p.IsDeleted = 0;
There are a few ways to execute this script with EF Core. One is to call this SQL script directly from the code. Another way to create a stored procedure or view and call that from the code.
Supposed I have the followings classes to map the result of executed SQL script by EF Core.
public partial class Blog
{
public int Id { get; set; }
public string Title { get; set; }
public string Slogan { get; set; }
public virtual ICollection<Post> Posts { get; set; }
}
public partial class Post
{
public int Id { get; set; }
public int BlogId { get; set; }
public string Title { get; set; }
public string PostContent { get; set; }
public virtual ICollection<PostTag> PostTags { get; set; }
}
public partial class Tag
{
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<PostTag> PostTags { get; set; }
}
public partial class PostTag
{
public int Id { get; set; }
public int PostId { get; set; }
public int TagId { get; set; }
public virtual Post Post { get; set; }
public virtual Tag Tag { get; set; }
}
This is a method in a controller:
[Route("posts/{blogId}")]
[HttpGet]
public async Task<IActionResult> GetBlogPosts(int blogId)
{
string sql = @"
SELECT b.Id,
b.Title,
p.Id,
p.Title,
p.PostContent,
t.Id,
t.Name
FROM dbo.Blogs b
JOIN Posts p ON p.BlogId = b.Id
LEFT JOIN PostTags pt ON pt.PostId = p.Id
LEFT JOIN Tags t ON t.Id = pt.TagId
WHERE b.Id = 1
AND p.IsDeleted = 0;
";
// this is not working
var result = db.Blogs.FromSql(sql).ToList().FirstOrDefault();
return Ok(result);
}
How I can map the result of the SQL script to the Blog object so that I can have the following result?
{
"Blog": [
{
"Id": 1,
"Title": "Another .NET Core Guy",
"Posts": [
{
"Id": 1,
"Title": "Post 1",
"PostContent": "Content 1 is about EF Core and Razor page",
"Tags": [
{
"Id": 1,
"Name": "Razor Page"
},
{
"Id": 2,
"Name": "EF Core"
}
]
},
{
"Id": 2,
"Title": "Post 2",
"PostContent": "Content 2 is about Dapper",
"Tags": [
{
"Id": 3,
"Name": "Dapper"
}
]
},
{
"Id": 4,
"Title": "Post 4",
"PostContent": "Content 4",
"Tags": [
{
"Id": 5,
"Name": "SqlKata"
}
]
}
]
}
]
}
Update August 13, 2019:
EF Core does not support this kind of feature yet as it has been stated here on EF Core Github page https://github.com/aspnet/EntityFrameworkCore/issues/14525
Popular Answer
Given you want an optimised query, and also the need to do additional where if required, you'll probably have to rely on linq-to-sql style to construct your query, and do the mapping to the desired results yourself:
var query = (
from b in db.Blogs
join p in db.Posts on b.Id equals p.BlogId
from pt in db.PostTag.Where(posttag => posttag.PostId == p.Id).DefaultIfEmpty() //LEFT OUTER JOIN
from t in db.Tags.Where(tag => tag.Id == pt.TagId).DefaultIfEmpty() //LEFT OUTER JOIN
where (...) //Your additional conditions
select new
{
BlogId = b.Id,
BlogTitle = b.Title,
PostId = p.Id,
PostTitle = p.Title,
p.PostContent,
TagId = (int?) t.Id,
TagName = t.Name
}).ToList();
From here on, you can either write the GroupBy statements yourself, or use some plugins.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow