how to convert raw sql subquery to linq query RSS RRS feed

  • Question

  • i have this query and i am unable to convert it to an equivalent linq query

    select distinct  u.Name,p.PName,u.AccCreateDate,up.BuyDate,u.ID from Users u
    inner join UserPackages up on up.U_ID=u.ID 
    inner join Packages p on p.PID=up.P_ID 
    where u.UserRef_No in
    (select  RefOf from Refers r inner join 
    Users u on r.RefNo=u.UserRef_No inner join UserPackages up on up.U_ID=u.ID where up.PackageStatus=1 )

    i tried this but it doens't work

    var referrals = (from u in dbs.Users
                                      join up in dbs.UserPackages on u.ID equals up.U_ID
                                      join p in dbs.Packages on up.P_ID equals p.PID
                                     where u.UserRef_No.Contains
                                      from rr in dbs.Refers
                                       join uu in dbs.Users on rr.RefNo
                                         equals uu.UserRef_No
                                       join upp in dbs.UserPackages on
                                        uu.ID equals upp.U_ID
                                       where upp.PackageStatus.Equals(1)
                                       select rr.RefOf)
                                     select new UsersHavingReferrals
                                         Id = u.ID,
                                         UserName = u.Name,
                                         PackageName = p.PName,
                                         AccCreateDate = u.AccCreateDate,
                                         PackageBuyDate = up.BuyDate


    i think this might be the answer though not sure so not gonna mark it as an answer 

     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 db.Users.Any
                                (sp => sp.UserRef_No == r.RefOf && sp.ID == UID)
                                && up.PackageStatus == true
                                select new ReferralsListView
                                    Name = u.Name,
                                    Package = p.PName,
                                    Price = p.Price,
                                    YouEarned = (p.Price * p.ReferCommission / 100)

    • Edited by Learner177 Sunday, November 15, 2020 1:43 PM possible answer found
    Sunday, November 15, 2020 12:42 PM

All replies

  • Linq is not the answer to everything. You can run raw T-SQL with EF or use a stored procedure and either way do a Tolist projected  into a list using a custom class.
    Sunday, November 15, 2020 3:24 PM