CODEWITHSUNDEEP
c#linqt-sqllinq-to-sqlunion
RPM1984
RPM1984

Reputation: 73122

Help Converting T-SQL to LINQ

Have the following (non-straightforward) T-SQL query, which i'm trying to convert to LINQ (to be used in a L2SQL expression):

declare @IdAddress int = 481887

select * from 
(
    select top 3 p.* 
    from tblProCon p
    inner join vwAddressExpanded a
    on p.IdPrimaryCity = a.IdPrimaryCity
    where a.AddressType = 3
    and p.IsPro = 1
    and a.IdAddress = @IdAddress
    order by AgreeCount desc
) as Pros

union

select * from 
(
    select top 3 p.* 
    from tblProCon p
    inner join vwAddressExpanded a
    on p.IdPrimaryCity = a.IdPrimaryCity
    where a.AddressType = 3
    and p.IsPro = 0
    and a.IdAddress = @IdAddress
    order by AgreeCount desc
) as Cons

order by ispro desc, AgreeCount desc

In a nutshell, i have an @IdAddress - and i'm trying to find the top 3 pro's and top 3 con's for that address.

The above query does work as expected. I'm not entirely sure how to convert it to a LINQ query (never done unions before with LINQ). I don't even know where to start. :)

Query-style/Lambda accepted (prefer query-style, for readability).

Also - i have LinqPad installed - but i'm not sure how to "convert T-SQL to Linq" - is there an option for that? Bonus upvote will be awarded for that. :)

The above T-SQL query performs well, and this L2SQL query will be executed frequently, so it needs to perform pretty well.

Appreciate the help.

Upvotes: 1

Views: 350

Answers (2)

Paul Creasey
Paul Creasey

Reputation: 28834

var baseQuery = (from p in db.tblProCon
                join a in db.vwAddresssExpanded
                on p.IdPrimaryCity equals a.IdPrimaryCity
                where a.AddressType == (byte) AddressType.PrimaryCity &&
          a.IdAddress == idAddress
          order by p.AgreeCount descending
                select p);

var pros = baseQuery.Where(x=> x.IsPro).Take(3);
var cons = baseQuery.Where(x=> !x.IsPro).Take(3);

var results = pros
               .Union(cons)
               .OrderByDescending(x => x.IsPro)
               .ThenByDescending(x => x.AgreeCount)
               .ToList();

Upvotes: 2

SLaks
SLaks

Reputation: 887449

You can call (some query expression).Union(other query expression).
You can also (equivalently) write Enumerable.Union(some query expression, other query expression).

Note that both expressions must return the same type.

AFAIK, there are no tools that automatically convert SQL to LINQ.
(For non-trivial SQL, that's a non-trivial task)

Upvotes: 0

Related Questions