Lookup using Entity Framework

Question

I am trying to do lookup cross 2 tables using Entity Framework unfortunately I couldn't find way but I do have sql query which does what I want

SQL Query

select top(1) p1.Percentage
from LookupTable p1 , [lookupTablePerUnit] p2
where p2.[LookupValue] &lt;= @Value1  and p1.ID=p2.[LookupID]
order BY pr.range DESC

if @Value1= 6 then The result = 52

Here is a screenshot of two tables (lookupTable && LookTablePerUnit) using this query

(SELECT * FROM [DB].[dbo].[lookupTablePerUnit] p1 , [DB].[dbo].[LookupTable] p2
where p1.LookupTableID = p2.ID)

http://s15.postimage.org/m80zvn4mx/Captur2e.png

Similar questions have been answered at http://stackoverflow.com/questions/296972/sql-to-linq-tool and http://stackoverflow.com/questions/1339732/convert-sql-to-linq-to-sql. — David, Mar 07 '12 at 02:40
I couldn't find the solution through the links you provided, even I tried this tool (LINQPAD). No luck .... :( — mattnz, Mar 07 '12 at 02:59

BrokenGlass · Accepted Answer · 2012-03-07T03:22:22.630

A join (your query is using the implicit join syntax for SQL) would look very similar in Linq to Entities:

var query = from p1 in context.LookupTable 
            join p2 in context.lookupTablePerUnit on p1.ID equals p2.LookupID
            where p2.LookupValue <= Value1
            orderby p1.range descending
            select p1.Percentage;

var result = query.FirstOrDefault();

Had to take a guess on the range property you have a typo in your question so its not clear whether it can be attributed to LookupTable or lookupTablePerUnit

score 2 · Answer 2 · answered Mar 07 '12 at 03:19

Something like this (hope you're using C#):

int value1 = 6;
int percentage = (from p1 in context.LookupTable
                 from p2 in context.lookupTablePerUnit
                 where p2.LookupValue <= value1 and p1.ID=p2.LookupID
                 orderby p2.range descending
                 select p1.Percentage).First();

Where context is your ObjectContext instance. Note that Entity Framework may erroneously pluralized your entity names, so LookupTable and LookupTablePerUnit may actually be something like LookupTables and lookupTablePerUnits in your ObjectContext (and lookupTablePerUnit may be capitalized).

Lookup using Entity Framework

2 Answers2