CODEWITHSUNDEEP
c#sqllinq
Arunesh
Arunesh

Reputation: 297

conditional operator in linq sql

Is it possible to have a conditional operator in linq in asp.net? I have tried this piece of code but it shows:

   The name BusinessInstance and LocalityMaster1 is not in a scope on the left hand side of 'equal' Consider swaping the expression on either side of 'equals'.

I need the result as BusinessName,(LocalityName/BusinessAddress),CityName. My sql is as follows:

from BusinessInstanceInfoKeys in db.utblYPBusinessInstanceInfoKeys.Where(x=> x.BusinessName.Contains(term))
join CityMaster in db.utblCityMasters
on BusinessInstanceInfoKeys.CityID equals CityMaster.CityID

let Address = BusinessInstanceInfoKeys.LocalityID != null
              ? from LocalityMaster1 in db.utblLocalityMasters
                join BusinessInstance in db.utblYPBusinessInstanceInfoKeys
                  on BusinessInstance.LocalityID equals LocalityMaster1.LocalityID
                select new 
                {
                    LocalityMaster1.LocalityName 
                }
              : BusinessInstanceInfoKeys.BusinessAddress
select new
{
    LocalityName = BusinessInstanceInfoKeys.BusinessName + ",(" + Address + ")" + "," + CityMaster.CityName
};

Can anyone suggest the right way?

Upvotes: 1

Views: 576

Answers (1)

Grant Winney
Grant Winney

Reputation: 66449

To get rid of the error, replace this:

on BusinessInstance.LocalityID equals LocalityMaster1.LocalityID

with this:

on LocalityMaster1.LocalityID equals BusinessInstance.LocalityID

Also, you can include a ternary expression inside of a let, but there may be a couple issues:

  • If you're querying directly against the database (i.e. Entity Framework), I don't know that it will be able to produce meaningful SQL from the ternary expression, so it may throw an exception.

  • Both conditions in the ternary expression must return the same data type. Yours do not appear to.

Based on your comments, and if at least one record will be returned if the query below is run, you can try this. (I'm assuming LocalityName and BusinessAddress are both strings.)

let Address = BusinessInstanceInfoKeys.LocalityID != null
              ? (from LocalityMaster1 in db.utblLocalityMasters
                 join BusinessInstance in db.utblYPBusinessInstanceInfoKeys
                   on BusinessInstance.LocalityID equals LocalityMaster1.LocalityID
                 select LocalityMaster1.LocalityName).FirstOrDefault()
              : BusinessInstanceInfoKeys.BusinessAddress

Upvotes: 1

Related Questions