KevinBurton

If I can get an answer to this then it will do two things. One, I will know how to return a value from the database rather than a query. And two, I will know how to return the results from a count and/or 'case' statement. Here is the stored procedure that I am trying to convert to a LINQ query:

Code Block

SELECT (CASE WHEN (SELECT COUNT(*) FROM IV00104 WHERE ITEMNMBR = @Sku) > 0 THEN 0

ELSE (CASE WHEN (SELECT COUNT(*) FROM IV00104 WHERE CMPTITNM = @Sku) > 0 THEN 1

ELSE 2 END) END) AS SkuType

Basically if the table has one or more items that match the first sub-query then a 0 is returned, if the table has one or more items that match the secon sub-query then a 1 is returned, and finally if there are not matches a 2 is returned. So this query will only return 0,1, or 2. Now, any suggestions on how to translate this to LINQ

Thank you.

Kevin



Re: LINQ Project General Simple LINQ query help.

Keith Farmer

Hmm...

It's straightforward to do it in multiple queries, but in one query

Code Block

(

from x in db.IV00104

let hasItems = db.IV00104.Any(y => y.Itemnmbr = sku)

let hasCmptit = db.IV00104.Any(y => y.Cmptitnm == sku)

select hasItems 0 : (hasCmptit 1 : 2)

).First()

I don't believe we have any direct support for selecting from no table, which is what you're doing. So we use one as a dummy, and just select the first row of the subquery.

(This would make an excellect suggestion for SP1 or v2, I think...)






Re: LINQ Project General Simple LINQ query help.

KevinBurton

I came up with something very similar. Are there holes in my implementation

Code Block

from k in db.IV00104

let partyCount = (from party in db.IV00104 where party.ITEMNMBR == variantId select party).Count()

let itemCount = (from component in db.IV00104 where component.CMPTITNM == variantId select component).Count()

select ((partyCount > 0) 0 : ((itemCount > 0) 1 : 2)))

I am not acquanted with 'Any' and 'First'. What is their function

Thank you.

Kevin





Re: LINQ Project General Simple LINQ query help.

Keith Farmer

Any(predicate) returns true if at least one item in the sequence returns true for the predicate. First() returns the first item in the sequence (and skips the rest).

Counting and using > 0 is equivalent to Any, except that you end up scanning the entire sequence (SQL Server might shortcut this, but LINQ to Objects probably wouldn't). Any would be more efficient.

Also, if I'm running your query in my head correctly, your query will evaluate the lets and select for each row in IV00104. Using First will halt evaluation after the first item is retrieved. It also returns T instead of IEnumerable<T>, so you can retrieve it directly.






Re: LINQ Project General Simple LINQ query help.

KevinBurton

Thanks again. A couple of last questions. In your code you have:

y.Itemnmbr = sku

Did you mean for the '=' to be '==' I want to scan the whole table for matches for either the first or second criteria. When you say that 'First' halts evaluation after the first item, what does that mean I seems that the most efficient would be to scan the table for matches of the first criteria and if found return '0' a and don't scan for the second criteria. Is that what you meant





Re: LINQ Project General Simple LINQ query help.

Keith Farmer

I did intend '=='.

Okay, so let's walk through your code as if it were LINQ to Objects. The walk-through for LINQ to SQL wouldn't be that much different, and it's probably easier to think about Objects at the moment.

Code Block

from k in db.IV00104

turns into a foreach over each item in db.IV00104,

Code Block

let partyCount = (from party in db.IV00104 where party.ITEMNMBR == variantId select party).Count()

let itemCount = (from component in db.IV00104 where component.CMPTITNM == variantId select component).Count()

sets some variables, and

Code Block

select ((partyCount > 0) 0 : ((itemCount > 0) 1 : 2)))

becomes the equivalent of a yield return.

In other words:

Code Block

foreach (var k in db.IV00104)

{

var partyCount = ...;

var itemCount = ...;

yield return ((partyCount > 0) 0 : ((itemCount > 0) 1 : 2)));

}

So yes, *if* you were writing it as an explicit foreach loop, you *could* shortcut after partyCount. That is, you could instead use:

Code Block

from k in db.IV00104

select (((from party in db.IV00104 where party.ITEMNMBR == variantId select party).Count() > 0) 0 : (((from component in db.IV00104 where component.CMPTITNM == variantId select component).Count()> 0) 1 : 2)))

and count on left-to-right evaluation to check for parties first. However consider what Count does on a regular IEnumerable: it pulls each item from the sequence, incrementing a counter as it goes, until that sequence ends. But you just want to know if *any* item matches -- you could return true on the first match and save yourself from scanning items 2 through 1 billion if the first item satisfies the condition. That's what Any does:

Code Block

from k in db.IV00104

select (((from party in db.IV00104 where party.ITEMNMBR == variantId select party).Any()) 0 : (((from component in db.IV00104 where component.CMPTITNM == variantId select component).Any()) 1 : 2)))

But that's hard to read. It can be written shorter using the dot-notation:

Code Block

from k in db.IV00104

select db.IV00104.Any(party => party.ITEMNMBR == variantId) 0 : (db.IV00104.Any(component => component.CMPTITNM == variantId) 1 : 2)

which is

Code Block

foreach (var k in db.IV00104)

{

yield return /* our projection */;

}

This is great, but we're still yielding this for each item in db.IV00104. First() returns the first item:

Code Block

var q =

from k in db.IV00104

select db.IV00104.Any(party => party.ITEMNMBR == variantId) 0 : (db.IV00104.Any(component => component.CMPTITNM == variantId) 1 : 2)

foreach (var item in q)

{

return item;

}

.. and the second item from q, onward, are never requested. In SQL, this is like "SELECT TAKE 1 ..." and recognizing you're only getting one item (as opposed to a sequence that contains only one item -- it's a subtle difference).