CODEWITHSUNDEEP
c#sqllinqentity-framework
Paul Lonsdale
Paul Lonsdale

Reputation: 1

Conversion of SQL into LINQ

I need help converting some an SQL select into LINQ

Here is the original SQL:

Select Top 1 IsNull(ct.Template, t.Template) as Template 
From Template t
Left Outer Join ClientTemplate ct On t.TemplateTypeId = ct.TemplateTypeId And ct.ClientId = 149
Where t.TemplateTypeId = (Select TemplateTypeId From QuoteType Where QuoteTypeId = 7)
Order By t.Version DESC, ct.Version DESC

I'm using Entity Framework and have entities for QuoteTypes, ClientTemplates and Templates.

The above SQL gets the Template from the ClientTemplate table for client 149 (uses a variable in the real code) for a particular QuoteType. If there is no entry in the CLientTemplate table then it returns the Template from the main Template table for the same QuoteType!

My idea was to query the QuoteType first and then query the ClientTemplate table to see if one exists and if not query the Template table instead. The problem is this will result in three queries but I'm sure it can be done in one swoop!?

Can anyone have a go at writing the LINQ for me?

Here's my mess so far:

QuoteType quoteType = (from qt in this.entities.QuoteTypes where qt.QuoteTypeID == this.SelectedNewQuoteTypeID select qt).First();
if (quoteType != null && quoteType.TemplateTypeID.HasValue)
{
    int quoteTypeTemplateTypeID = (int)quoteType.TemplateTypeID;

    var query = (from t in this.entities.Templates
             join ct in this.entities.ClientTemplates on t.TemplateTypeID equals ct.TemplateTypeID 
             into a
             from b in a.DefaultIfEmpty(new ClientTemplate())
             where t.TemplateTypeID == quoteTypeTemplateTypeID
             orderby t.Version descending
             select new
             {
                T1 = t.Template1,
                T2 = b.Template
             }).First();

    // Check the query to see if T1 and T2 are null and use whichever one isn't!
    // TODO !!!
}
else
{
    return string.Empty;
}

I kind of gave up once I got that far and posted this! My example still does two queries and does not select based on the client ID. It also does not have the second order by on the client template table.

I've inherited the original SQL statement so maybe the problem is with that being badly written in the first place!?

Over to you...

Upvotes: 0

Views: 119

Answers (2)

CSharper
CSharper

Reputation: 5580

I think this might work, although this is untested, from reading it appears you can't add multiple join conditions to a LinQ statement so you can just specify it in the where clause

So here ya go, I tried to convert the SQL word for word so here's my attempt. If it does everything I think it will do it will work.

int templateTypeId;
templateTypeId= (context.QuoteType.Where(x => x.QuoteTypeId == 7)).FirstOrDefault().TemplateTypeId

var qry =(

from t in context.Template
join ct in context.ClientTemplate on
t.TemplateTypeId equals ct.TemplateTypeId  into cts

from ct in cts.DefaultIfEmpty()  //left join

where t.TemplateTypeId == templateTypeId
        && ct.ClientId == 149   
 order by t.Version descending, ct.Version descending
select new 
{
  Template = (ct.Template != null) ? ct.Template : t.Template //ternary operator
}).FirstOrDefault();

Upvotes: 0

hdlopez
hdlopez

Reputation: 295

I haven't tested it but maybe you could try something like this

from t in this.entities.Template
from ct in this.entities.ClientTemplate.Where(x => t.TemplateTypeId == ct.TemplateTypeId && ct.ClientId == 149).DefaultIfEmpty()
where t.TemplateTypeId == (from x in this.entities.QuoteType where x.QuoteTypeId == 7 select x.TemplateTypeId).FirstOrDefault()
orderby t.Version descending, ct.Version descending
select new { ct.Template == null ? t.Template : ct.Template }

Upvotes: 1

Related Questions