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

  • Question

  • User-1257308419 posted

    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
    
                                     }).Distinct().ToList();

    Sunday, November 15, 2020 11:55 AM

All replies

  • User475983607 posted

    The TSQL can be refactored using a basic join rather than a WHERE IN.  I believe this will the following code will work but I'm unable to test.  

    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 
        inner join Refers r on r.RefNo = u.UserRef_No
    where up.PackageStatus = 1

    Sunday, November 15, 2020 12:50 PM
  • User-1257308419 posted

    thanks i hve done that already using this query

     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
                                                     join r in dbs.Refers on u.UserRef_No equals r.RefOf
                                                     where u.UserRef_No.Equals(r.RefOf)
    
                                                     select new UsersHavingReferrals
                                                     {
                                                         Id = u.ID,
                                                         UserName = u.Name,
                                                         PackageName = p.PName,
                                                         AccCreateDate = u.AccCreateDate,
                                                         PackageBuyDate = up.BuyDate
    
                                                     }).Distinct().ToList();

    but i want to know how to do this if i need to do this in future using IN

    you can consider this code as an example as i am unable to do it with linq join 

    select distinct  u.Name
    ,p.PName,p.Price,(p.Price*p.ReferCommission/100)as youearned,
    up.BuyDate
    from Users u  inner join UserPackages up on up.U_ID=u.ID 
    inner join Packages p on p.PID=up.P_ID
    inner join Refers r on u.Ref_No=r.RefOf
    
    where r.RefOf in(select distinct UserRef_No from Users u where u.ID=29 )
    and up.PackageStatus=1

    Sunday, November 15, 2020 12:56 PM
  • User-1257308419 posted

    select distinct  u.Name
    ,p.PName,p.Price,(p.Price*p.ReferCommission/100)as youearned,
    up.BuyDate
    from Users u  inner join UserPackages up on up.U_ID=u.ID 
    inner join Packages p on p.PID=up.P_ID
    inner join Refers r on u.Ref_No=r.RefOf
    
    where r.RefOf in(select distinct UserRef_No from Users u where u.ID=29 )
    and up.PackageStatus=1

     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)
                                }).Distinct().ToList();

    i have written this linq for my purpose i don't know if it really is equivalent to the above sql i cannot test it for too much data but for now i am getting correct results

    Sunday, November 15, 2020 1:32 PM
  • User475983607 posted

    you can consider this code as an example as i am unable to do it with linq join 

    If you want to move the poorly designed TSQL to LINQ, then create a query that returns a list.  Use the list to the LINQ query with the "Contains()" extension.  Basically, you'll create two LINQ quires just like the TSQL script.

    https://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause

    Sunday, November 15, 2020 1:34 PM
  • User-1257308419 posted

    Basically, you'll create two LINQ quires just like the TSQL script

    that is exactly what i don't want to do...that's the reason i switched from first example to second example because first eg was able to be converted to a join instead of using IN so i did that but in case of 2nd eg its not possible as per my knowledge that's the sole reason i asked what to do in that case as sometimes we have to write IN queries which can't be converted to JOIN

    Sunday, November 15, 2020 1:40 PM
  • User475983607 posted

    that is exactly what i don't want to do...that's the reason i switched from first example to second example because first eg was able to be converted to a join instead of using IN so i did that but in case of 2nd eg its not possible as per my knowledge that's the sole reason i asked what to do in that case as sometimes we have to write IN queries which can't be converted to JOIN

    Your second LINQ does NOT create an IN clause.  It creates a WHERE EXISTS sub query which is NOT what you asked.   The "Contains()" extension will create an "IN" clause.

    Sunday, November 15, 2020 2:40 PM
  • User-1257308419 posted

    "Contains()" extension will create an "IN" clause.

    Can you provide an example how to use contain in my case without using 2 queries as I already know I need to use contain but I don't know how to use it in my case. 

    Sunday, November 15, 2020 2:45 PM
  • User475983607 posted

    Learner94

    Can you provide an example how to use contain in my case without using 2 queries as I already know I need to use contain but I don't know how to use it in my case. 

    As far as I know, it's not possible in LINQ.  LINQ wants to create optimized TSQL.

    If we consider a well designed schema then the predicate, WHERE col IN (SELECT col from Table), can typically be refactored into a JOIN.  There's no good reason to use the "IN" when you can use a JOIN.  The "IN" clause gets tricky, at least different than a join,  if there are any NULLs in the result set or duplicate values. 

    I'm sure we could come up with edge cases and in those instances I would execute a RAW query. 

    Sunday, November 15, 2020 3:06 PM