how to convert raw sql to entity linq

Question

select distinct Users.Name,Packages.PName,Packages.Price,RefEarning=(select Price*ReferCommission/100)
from Users inner join UserPackages on UserPackages.U_ID=Users.ID
inner join Packages on Packages.PID=UserPackages.P_ID
inner join Refers on Users.Ref_No=Refers.RefOf
where exists(
select Users.ID from users where Refers.RefOf='Gerfwscv6kOwnIM8hl_TiA'
) and UserPackages.PackageStatus='true'

Answer 1

Hi @Shahzaib khan ，

I recommend you could try online converter:

Linqer : sqltolinq

You can reference : linqer-a-nice-tool-for-sql-to-linq-transition ,

An SQL-> LINQ converter.

Or LINQPad: linqpad

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

got it working with this

var data = (from u in db.Users join up in db.UserPackages on u.ID equals up.U_ID
                        join p in db.Packages on up.P_ID equals p.PID
                        join r in db.Refers on u.Ref_No equals r.RefOf where /*(from u2 in db.Users select u2.ID).Any() && */ r.RefOf.Equals("GDnA47D6XkOwnIM8hl_TiA") && up.PackageStatus == true
                        select new ReferralsListView
                        {
                            Name = u.Name,
                            Package = p.PName,
                           Price = p.Price,
                            YouEarned = (p.Price * p.ReferCommission / 100),


                        }).Distinct().ToList(); 

Answer 3

I've been working on a sideproject called LINQ Me Up (www.linqmeup.com) that uses AI to convert SQL into C# LINQ code.

It's SaaS, will cost you $8 p/month and helps to create good conversions within minutes.

This is the result that LINQ ME Up gave me:

var query = from visit in dbo.Visit
            join guestHouseBooking in dbo.GuestHouseBooking on visit.ID equals guestHouseBooking.VisitID into g
            from guestHouseBooking in g.DefaultIfEmpty()
            where visit.ArrivalDate.Month == 8 && visit.ArrivalDate.Year == 2022 && visit.Status == "Approved"
            group visit by new { visit.VisitorType1, visit.ArrivalDate.Year, visit.ArrivalDate.Month, visit.Status } into g
            select new
            {
                Status = g.Key.Status,
                Year = g.Key.Year,
                Month = g.Key.Month,
                Type = g.Key.VisitorType1,
                Count = g.Count(),
                Sum = g.Sum(x => x.VisitorNum),
                GHbooking = g.Count(x => x.GuestHouseBooking != null)
            };

var result = query.ToList();

Answer 4

I think you can convert SQL to LINQ using chatGPT

https://chat.openai.com/chat