sticky
Entity frmaework Convert SQL to LINQ statement.

    General discussion

  • During entity framework developing, sometimes we need to convert SQL statement to LINQ, here are sample common method for your reference.

    #Table Structure.

    CREATE TABLE [dbo].[Blog] (
    
        [BlogId]     INT          IDENTITY (1, 1) NOT NULL,
    
        [Url]        VARCHAR (50) NOT NULL,
    
        [Categroy]   VARCHAR (50) NOT NULL,
    
        [Author]     VARCHAR (50) NOT NULL,
    
        [PostNumber] INT          NOT NULL
    
    );
    
    CREATE TABLE [dbo].[Post] (
    
        [PostId]   INT            IDENTITY (1, 1) NOT NULL,
    
        [BlogId]   INT            NOT NULL,
    
        [Content]  NVARCHAR (MAX) NULL,
    
        [Title]    NVARCHAR (MAX) NULL,
    
        [PostTime] DATETIME       NULL
    
    );
    
    #Model Class
    
    [Table("Blog")]
    
        public partial class Blog
    
        {
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    
            public Blog()
    
            {
    
                Posts = new HashSet<Post>();
    
            }
    
            public int BlogId { get; set; }
    
            [Required]
    
            [StringLength(50)]
    
            public string Url { get; set; }
    
            [Required]
    
            [StringLength(50)]
    
            public string Categroy { get; set; }
    
            [Required]
    
            [StringLength(50)]
    
            public string Author { get; set; }
    
            public int PostNumber { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    
            public virtual ICollection<Post> Posts { get; set; }
    
    }
    
        [Table("Post")]
    
        public partial class Post
    
        {
    
            public int PostId { get; set; }
    
            public int BlogId { get; set; }
    
            public string Content { get; set; }
    
            public string Title { get; set; }
    
            public DateTime? PostTime { get; set; }
    
            public virtual Blog Blog { get; set; }
    
        }

    1. Grouping Operators

        1). group multiple columns

    SQL:
    
    select b.Author, b.Categroy from blog b group by b.Author, b.Categroy
    
    LINQ:
    
    var result = db.Blogs.GroupBy(t => new { t.Author, t.Categroy }, (key, grp) => new { key.Author, key.Categroy}).ToList();

    2). Group and Max.

    SQL:
    
    select b.Categroy,MAX(PostNumber) as MaxNumber from Blog b group by Categroy
    
    LINQ:
    
    var result = db.Blogs.GroupBy(t => t.Categroy, (key, grp) => new { Categroy = key, MaxNumber = grp.Max(m => m.PostNumber) }).ToList();

       3). group by and count.

    SQL:
    
    select b.Categroy, count(*) as Count from Blog b group by Categroy
    
    LINQ:
    
    var result = db.Blogs.GroupBy(t => t.Categroy, (key, grp) => new { Categroy = key, Count = grp.Count() }).ToList();

    4). group by and sum

    SQL:
    
     select b.Categroy, sum(b.PostNumber) as Count from Blog b group by Categroy
    
    LINQ:
    
    var result = db.Blogs.GroupBy(t => t.Categroy, (key, grp) => new { Categroy = key, Count = grp.Sum(s=>s.PostNumber) }).ToList();

    5). group and having

    SQL:
    
    select B.Categroy
    
     from Blog B
    
     group by B.Categroy
    
     having COUNT(1) > 1
    
    SQL:
    
    var result = db.Blogs.GroupBy(t => t.Categroy).Where(grp => grp.Count() > 1).Select(key => key).ToList();

    6).  group and get the first row

    SQL:
    
        SELECT
    
        1 AS [C1],
    
        [Project3].[Categroy] AS [Categroy],
    
        [Project3].[C1] AS [C2]
    
        FROM ( SELECT
    
            [Distinct1].[Categroy] AS [Categroy],
    
            (SELECT TOP (1) [Project2].[Url] AS [Url]
    
                FROM ( SELECT
    
                    [Extent2].[Url] AS [Url]
    
                    FROM [dbo].[Blog] AS [Extent2]
    
                    WHERE [Distinct1].[Categroy] = [Extent2].[Categroy]
    
                )  AS [Project2]
    
                ORDER BY [Project2].[Url] DESC) AS [C1]
    
            FROM ( SELECT DISTINCT
    
                [Extent1].[Categroy] AS [Categroy]
    
                FROM [dbo].[Blog] AS [Extent1]
    
            )  AS [Distinct1]
    
    )  AS [Project3]
    
           LINQ:
    
    var result = db.Blogs.GroupBy(t => t.Categroy, (key, grp) => new { Categroy = key, FirstUrl = grp.OrderByDescending(m => m.Url).FirstOrDefault().Url }).ToList();

    2. Join Operators

     1). Inner Join.

    SQL:
    
    select b.Author, b.Categroy, b.Url, p.Content, p.Title from Blog b
    
    join Post p on b.BlogId = p.BlogId
    
                         LINQ:
    
                                 var result5 = db.Blogs.Join(db.Posts,
    
                        b => b.BlogId,
    
                        p => p.BlogId,
    
                        (b, p) => new
    
                        {
    
                            Author= b.Author,
    
                            Categroy = b.Categroy,
    
                            Url = b.Url,
    
                            Content = p.Content,
    
                            Title = p.Title
    
                        }
    
                        ).ToList();

    2). Left Join.

    SQL:
    
    select b.Author, b.Categroy, b.Url, p.Content, p.Title from Blog b
    
    left outer join Post p on b.BlogId = p.BlogId
    
    LINQ:
    
    var result = (from b in db.Blogs
    
                                  join p in db.Posts on b.BlogId equals p.BlogId into grp
    
                                  from sub in grp.DefaultIfEmpty()
    
                                  select new {
    
                                      Author = b.Author,
    
                                      Categroy = b.Categroy,
    
                                      Url = b.Url,
    
                                      Content = sub.Content,
    
                                      Title = sub.Title
    
                                  }).ToList();

    3. Union Operators

    SQL:
    
         select b.Author as U1, b.Categroy as U2 from Blog b
    
                   union
    
          select p.Content as U1, p.Title as U2 from Post p
    
    LINQ:
    
            var result = db.Blogs.Select(t => new { U1 = t.Categroy, U2 = t.Url }).Union(db.Posts.Select(t => new { U1 = t.Title, U2 = t.Content })).ToList();

    4. Query Execution.

    1). Filter records from main table via child’s table condition.

    SQL:
    
           select b.* from Blog b join Post p on b.BlogId = p.BlogId where p.Content like '%AAA%'
    
    LINQ:
    
    var result = db.Blogs.Where(b => b.Posts.Any(p => p.Content.Contains("AAA"))).ToList();

    2). Top 1

    SQL:
    
    Select top 1 from blog
    
    LINQ:
    
    var result = db.Blogs.FirstOrDefault();



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 18, 2019 9:18 AM
    Moderator