CODEWITHSUNDEEP
c#sqllinq
mlynn
mlynn

Reputation: 59

SQL to LINQ conversion C#

I'm trying to convert this sql statement to linq and need some assistance:

SELECT * 
FROM userlocation ul 
       INNER JOIN wins_user w 
               ON ul.locationname = w.location 
WHERE ul.locationname = 'Value' 
        OR ( NOT EXISTS(SELECT * 
                        FROM mulitcustomeraccess 
                        WHERE userid = 'Value') )

Here is my Linq code (usr is WINS_USER table):

billcodelist = String.Join(
    ",", 
    dc.USERLOCATIONs
        .Where(f => f.LOCATIONNAME == usr.LOCATION || 
               dc.MULITCUSTOMERACCESSes
                   .Where(d => d.USERID == usr.Name)
                   .Select(d => d.LOCATIONNAME)
                   .Contains(f.LOCATIONNAME))
        .Select(f => f.BILLCODECUSTNUMLIST)
        .ToArray());

I tried updating my linq code to this

billcodelist = String.Join(
    ",", 
    dc.USERLOCATIONs
        .Where(f => f.LOCATIONNAME == usr.LOCATION || 
               !dc.MULITCUSTOMERACCESSes
                   .Any(d => d.USERID == usr.Name)
                   .Select(d => d.LOCATIONNAME)
                   .Contains(f.LOCATIONNAME))
        .Select(f => f.BILLCODECUSTNUMLIST)
        .ToArray());

But then I get the following error :

'bool' does not contain a definition for 'Select' and no accessible extension method 'Select' accepting a first argument of type 'bool' could be found (are you missing a using directive or an assembly reference?) error.

My question is how do I convert that SQL to linq, what am I doing incorrectly?

Upvotes: 2

Views: 111

Answers (2)

Igor
Igor

Reputation: 62298

var results = USERLOCATION.Join(db.WINS_USER, w => w.LOCATION, ul => ul.locationname, (w, ul) => new {w, ul})
        .Where(_ => _.ul.LOCATIONNAME == 'Value' || !db.MULITCUSTOMERACCESS.Any(m => m.USERID == 'Value'))
        .Select(_ => _.ul.BILLCODECUSTNUMLIST);

var billCodeList = string.Join(",", results);

Where clauses always expect boolean expressions, you are passing a where into a where but a where does not return a boolean but rather an IQueryable. In the above I used Any to evaluate if MULITCUSTOMERACCESS has a record where you had used Where.

Upvotes: 0

haku
haku

Reputation: 4505

Here is another alternative

 var result = from ul in UserLocation
           join winUser in Wins_User on ul.locationName equals winUser.Location
           where ul.locationName == 'value' 
              || !MultiCustomerAccess.Any(x=> x.userId == "value")
           select new { // your projections.}

Upvotes: 1

Related Questions