CODEWITHSUNDEEP
c#sqllinq
ailerifren
ailerifren

Reputation: 97

how to do something like a "case when" condition in linq?

basically i have some data table similar to this

| ItemId | RuleId | IsActive |
------------------------------
| ItemA  | RuleA  | FALSE    |
------------------------------
| NULL   | RuleA  | TRUE     |
------------------------------
| ItemC  | RuleA  | TRUE     |
------------------------------
| ItemA  | RuleB  | FALSE    |
------------------------------
| NULL   | RuleB  | TRUE     |
------------------------------

and i want to get result like this

for item a

| ItemId | RuleId | IsActive |
------------------------------
| ItemA  | RuleA  | FALSE    |
------------------------------
| ItemA  | RuleB  | FALSE    |
------------------------------

for item b

| ItemId | RuleId | IsActive |
------------------------------
| NULL   | RuleA  | TRUE     |
------------------------------
| NULL   | RuleB  | TRUE     |
------------------------------

for item c

| ItemId | RuleId | IsActive |
------------------------------
| ItemC  | RuleA  | TRUE     |
------------------------------
| NULL   | RuleB  | TRUE     |
------------------------------

basically if there's specific rule, then use the specified, otherwise use the default rule setting with null as itemId

how to achieve this in linq?

Thanks in advance

EDIT: thanks for styling, @lazyberezovsky. @dasblinkenlight I don't have the query for sql either. I do need in linq way, but it's also of great help if anyone can give me some advice how to get the result in sql way.

Upvotes: 1

Views: 481

Answers (3)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236248

You can group all items by RuleId, then select first item from each group, which has specified itemId. If there is no such item, then create and return default item:

from i in items
group i by i.RuleId into g
select g.FirstOrDefault(x => x.ItemId == itemId) ?? 
       new Item { ItemId = null, RuleId = g.Key, IsActive = true }

This query will work with in-memory items. But you can't create new default item on server side - you will receive an error

The entity or complex type 'Item' cannot be constructed in a LINQ to Entities query.

So, return anonymous types instead of items, and construct items in memory:

(from i in context.Items
 group i by i.RuleId into g
 let item = g.FirstOrDefault(x => x.ItemId == itemId)
 select new
 {
     ItemId = item == null ? null : itemId, // default if not found
     RuleId = g.Key,
     IsActive = item == null ? true : item.IsActive
 })
.AsEnumerable() // move to memory
.Select(i => new Item { 
     ItemId = i.ItemId, 
     RuleId = i.RuleId, 
     IsActive = i.IsActive 
});

That works with EF.

Upvotes: 0

Doctor Jones
Doctor Jones

Reputation: 21664

This isn't the prettiest LINQ query, but here's a complete code sample that works

//the sample data as provided by the OP
var rules = new []
{
    new { ItemId = "ItemA", RuleId = "RuleA", IsActive = false },
    new { ItemId = (string)null, RuleId = "RuleA", IsActive = true },
    new { ItemId = "ItemC", RuleId = "RuleA", IsActive = true },
    new { ItemId = "ItemA", RuleId = "RuleB", IsActive = false },
    new { ItemId = (string)null, RuleId = "RuleB", IsActive = true },
};

//the items that we want to get the rules for
var items = new [] { "ItemA", "ItemB", "ItemC", };

//get the rules for all of the items
var query = from i in items
            let itemRules = from r in rules
                            //we need to prefer rules that match our id
                            orderby r.ItemId ?? "" descending
                            //match on id or null
                            where (r.ItemId ?? i) == i                            
                            group r by r.RuleId into grouping                        
                            //take the best match
                            select grouping.First()
            select new
            {
                i,
                itemRules,
            };

and here's the output

enter image description here

I don't think this will work with LINQ to SQL but it certainly works with LINQ to Objects. I'll see if I can get a version working with LINQ to SQL when I've got some time to build a test DB.

Upvotes: 1

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

You can use any language construct in LINQ (it's not called Language Integrated Query for nothing). For example if you use VB.NET - you can use CHOOSE function directly inside of LINQ query

Upvotes: 0

Related Questions