What would be the linq equivalent to the below sql?

Question

How do I convert this sql query to linq equivalent? I have included my models. I am using EF code first from database. I am new to c# programming so I may not know what to include in my questions. Thank you

SELECT        RestaurantFranchiseeEmail.Email
FROM            RestaurantVersions INNER JOIN
                     RestaurantFranchiseeEmail ON 
RestaurantVersions.RestaurantId = RestaurantFranchiseeEmail.RestaurantId
WHERE        (RestaurantVersions.VersionId = N'M1')
GROUP BY RestaurantFranchiseeEmail.Email, RestaurantVersions.VersionId

Models

 namespace 
    {
        using System.ComponentModel.DataAnnotations;

        public class RestaurantVersion
    {
        public int Id { get; set; }

        [StringLength(50)]
        public string RestaurantId { get; set; }

        [StringLength(150)]
        public string Franchisee { get; set; }

        [StringLength(150)]
        public string VersionId { get; set; }
    }
}

   namespace 
    {
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;

    [Table("RestaurantFranchiseeEmail")]
    public class RestaurantFranchiseeEmail
    {
        public int Id { get; set; }

        [StringLength(50)]
        public string RestaurantId { get; set; }

        [StringLength(150)]
        public string Franchisee { get; set; }

        [StringLength(150)]
        public string Email { get; set; }
    }
}

Usen the linqpad. It sometimes helps you with the equivalent and you can practice the query and see the final result in sql — Zinov, Feb 06 '19 at 02:11
Perhaps using my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you. — NetMage, Feb 06 '19 at 19:51

score 0 · Answer 1 · answered Feb 06 '19 at 01:41

0

Try this code:

from s in RestaurantVersions 
          join c in RestaurantFranchiseeEmail on s.RestaurantId equals c.RestaurantId
         where c.VersionId == "M1"
           group c by s.Email && c.VersionId
        select c.Email;

answered Feb 06 '19 at 01:41

Bijay Yadav

928
1
9
22

score 0 · Answer 2 · answered Feb 06 '19 at 01:43

Since there isn't much detail to the question, I'm going to base my answer off of Entity Framework using ViewModels:

var result = _context.RestaurantVersions
    .Where(restaurantVersion => restaurantVersion.VersionId == "M1")
    .Select(restaurantVersion => new RestaurantVersionViewModel
    {
        VersionId = restaurantVersion.VersionId,
        RestaurantFranchiseeEmailViewModel = new RestaurantFranchiseeEmailViewModel
        {
            Email = restaurantVersion.RestaurantFranchiseeEmail.Email
        }
    })
    .GroupBy(restaurantVersionViewModel => new
        { 
            restaurantVersionViewModel.VersionId, 
            restaurantVersionViewModel.RestaurantFranchiseeEmailViewModel.Email 
        })
    .ToList()

This is untested and assumes a lot. Pure stab in the dark.

Edit: You'll have to grab the email out of the grouping.

score 0 · Answer 3 · answered Feb 06 '19 at 02:11

 var  query = from s in RestaurantVersions 
              join q in RestaurantFranchiseeEmail 
              on s.RestaurantId = 
                 q.RestaurantId
              where s.VersionId = N'M1'
              group q by q.Email
              select  q.Email;

I hope this information will be help you Or you can check this link enter link description here

What would be the linq equivalent to the below sql?

3 Answers3