Convert from SQL to LINQ in c# (.Net)

Question

I want to convert this from SQL to Linq but I don't know the exact syntax:

SELECT TOP 1 
    R.GalleryId a, COUNT(*) b, G.Address ad
FROM 
    [PiArt].[dbo].[Rents] R, [PiArt].[dbo].[Galleries] G 
WHERE
    R.GalleryId = G.GalleryId
GROUP BY
    R.GalleryId, G.Address
ORDER BY 
    COUNT(*) DESC

all the things i tried are out of context i cant grap of linq syntaxe this sould be returned into list but i dont know how all i know is how to write this insql — Salma Cherif, Oct 28 '17 at 18:43
How does a person learn from a conversion tool? This is not a duplicate question. Putting on hold is not productive. — jdweng, Oct 28 '17 at 23:02

score 0 · Answer 1 · answered Oct 28 '17 at 19:18

Try following

    class Program
    {
        static void Main(string[] args)
        {
            List<Gallery> galleries = new List<Gallery>();
            List<Rent> rents = new List<Rent>();


            var results = (from r in rents
                           join g in galleries on r.GallerId equals g.GallerId
                           select new { r = r, g = g })
                           .GroupBy(x => new { id = x.r.GallerId, address = x.r.Address })
                           .Select(x => new {
                               count = x.Count(),
                               id = x.Key.id,
                               address = x.Key.address
                           })
                           .OrderByDescending(x => x.count)
                           .FirstOrDefault();
        }
    }

    public class Gallery
    {
        public int GallerId { get; set; }
    }
    public class Rent
    {
        public int GallerId { get; set; }
        public string Address { get; set; }
    }

score 0 · Answer 2 · answered Oct 28 '17 at 19:22

you can try this.

from R in Rents
join G in Galleries on R.GalleryId equals G.GalleryId
group new {R,G} by new { R.GalleryId , G.Address}  into GRP 
orderby GRP.Count() descending
select new { 
                a= GRP.Key.GalleryId, 
                b = GRP.Count(), 
                ad = GRP.Key.Address  
            }

Convert from SQL to LINQ in c# (.Net)

2 Answers2