Help Converting T-SQL to LINQ

Have the following (non-straightforward) T-SQL query, whi开发者_运维技巧ch 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.

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();

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)