SQL语句在EFCore中的简单映射

SELECT

LINQ查询

var result = context.Blogs.Select(b => new { b.Id, b.Name }).ToList();

WHERE

LINQ Where

var filtered = context.Blogs.Where(b => b.IsActive).ToList();

ORDER BY

LINQ OrderBy/ThenBy

var ordered = context.Blogs.OrderBy(b => b.Name).ThenBy(b => b.Id).ToList();

GROUP BY

LINQ GroupBy

var grouped = context.Blogs.GroupBy(b => b.Category).Select(g => new { Category = g.Key, Count = g.Count() }).ToList();

HAVING

LINQ Where after GroupBy

var filteredGroups = context.Blogs.GroupBy(b => b.Category).Where(g => g.Count() > 1).Select(g => new { Category = g.Key, Count = g.Count() }).ToList();

JOIN

LINQ Join/GroupJoin

var joined = context.Blogs.Join(context.Posts, b => b.BlogId, p => p.BlogId, (b, p) => new { Blog = b, Post = p }).ToList();

LEFT JOIN

LINQ GroupJoin + DefaultIfEmpty

var leftJoined = context.Blogs.GroupJoin(context.Posts, b => b.BlogId, p => p.BlogId, (b, posts) => new { Blog = b, Posts = posts.DefaultIfEmpty() }).SelectMany(x => x.Posts, (b, p) => new { Blog = b.Blog, Post = p }).ToList();

INSERT

DbSet.Add/AddRange

context.Blogs.Add(new Blog { Name = "New Blog" }); context.SaveChanges();

UPDATE

修改实体后SaveChanges

var blog = context.Blogs.Find(1); blog.Name = "Updated Blog"; context.SaveChanges();

DELETE

DbSet.Remove/RemoveRange

var blog = context.Blogs.Find(1); context.Blogs.Remove(blog); context.SaveChanges();

AGGREGATE FUNCTIONS (如 COUNT, MAX, MIN, SUM, AVG)

LINQ Aggregate Functions

var count = context.Blogs.Count(); var maxId = context.Blogs.Max(b => b.Id);