星空雅梦

EF的连表查询Lambda表达式和linq语句

 

var lst = from c in db.Blogs join p in db.Posts on c.Id equals p.BlogId where p.Id==1 select c;
var lst1 = db.Blogs.Join(db.Posts.Where(p=>p.Id==1), b=> b.Id, p=> p.BlogId, (b, p) => new {b});

 public class Blog
 {
    public int Id { get; set; }
    public string Title { get; set; }
 }


 public class Post
 {
    public int Id { get; set; }
    public string Title { get; set; }
    public int BlogId { get; set; } 
 }

        public List<CommentsViewModel> GetCommentsByPid(int pid, int uid)
        {
            var query = from a in db.Photos
                        join b in db.Comments on a.PID equals b.PID
                        join c in db.Users on b.UID equals c.UID
                        where a.PID == pid
                        select new CommentsViewModel { Id = b.ID, Author = c.UserName, Comment = b.Content, UserAvatar = c.HeadPic, CanDelete = uid == b.UID ? true : false, CanReplay = true, TempDate = b.CreateTime, ParentId = b.ParentId };
            var ret = query.ToList();
            return ret;

       //或者
            var data = db.Photos.
                    Join(db.Comments, p => p.PID, c => c.PID,
                    (p, c) => new { p, c }).
                    Join(db.Users, n => n.c.UID, u => u.UID, (n, u) => new { n, u })
                    .Where(m => m.n.p.PID == pid)
                    .Select(m => new CommentsViewModel
                    {
                        Id = m.n.c.ID,
                        Author = m.u.UserName,
                        Comment =m.n.c.Content,
                    });
            return data.ToList();
        }