converting a t-sql query to Linq to Sql with coalesce and multiple join

We have two related tables (inventory, inventoryLocalization) and a t-sql query works on them as follows..

select inv.[code],
       coalesce(inL.name, inL2.name) as [name],
       coalesce(inL.description, inL2.description) as [description]
from dbo.[inventory] inv
left join dbo.[inventoryLocalization] inL on inv.code  = inL.code and inL.language = 'de'
left join dbo.[inventoryLocalization] inL2 on inv.code  = inL2.code and inL2.language = 'en'
where inv.[code] like '15.' + '%'
order by inv.[code];

to performance improvement, we will try to use these tables from cache not from RDBMS directly. for an asp.net 4.0 project, by c#. We've took both tables to chache with a DataSet and we can reach both of them through that DataSet.

DataSet ds = UtilityCache.getCachedDataSet();

"ds.Tables[0]" is inventory Table and "ds.Tables[1]" is inventoryLocalization Table

But how can we convert above T-Sql syntax to li开发者_JS百科nq2Sql. is it possible to use coalesce for select statement and to join on two tables with multiple conditions ?

Thanks in advance..

The coalesce can be done simply with the c# ?? coalescing operator, I.e.

select x.Name ?? y.Name;

Joins are single condition in LINQ, but in most cases (at least, for inner joins) you can add an extra "where" for the same result. This unfortunately is harder for outer joins :(

Also keep in mind that when the translation becomes suboptimal or just confusing, you can use:

var data = ctx.ExecuteQuery<ResultType>(tsql, args).ToList();

using {0} etc to represent parameters (like string.Format specifiers), and where ResultType can be any type with property names that match the returned column names (I often use a query-specific local type for this purpose).