CODEWITHSUNDEEP
c#linq-to-sql
Bob Fury
Bob Fury

Reputation: 537

Conditional projection with LINQ to SQL

Is it possible to do conditional projection with LINQ to SQL? Let's say I have one SQL table called PersonTable. In C#, I have a few classes:

public interface Person
{
 int employeeType; //1 is employee, 2 is manager
 String name;
}

and two derived classes. For simplicity sake:

public class Employee : Person {}
public class Manager : Person {}

Now, I want to use LINQ to SQL to project to the appropriate derived class based on employeeType:

IQueryable<IPerson> = PersonTable.Select(x => //x.employeeType == 1 ? new Employee { } : x == 2 {new Manager {} )

This would be ok for this situation, but I have a situation where I'm trying to instantiate 7 different possible derived types, so the conditional would get long and ugly really fast. Also, my real world scenario, the interface/derived classes have 15 or so properties to populate; which would also get long and ugly quickly.

My first attempt was to write an Expression Tree which returned the appropriate type, but that doesn't work because I need to know the value of the parameter expression to call Expression.MemberInit correctly. What solution might be best for a large conditional initialization like this with LINQ to SQL.

I can't use Table Inheritance, because I'm creating data transfer objects within the projections. I've also thought about using where and union, but the expression tree route seems more intriguing in that I'd have one spot to maintain the code instead of every time a new derived class is introduced - if this is an even an option.

Upvotes: 2

Views: 1317

Answers (1)

Jodrell
Jodrell

Reputation: 35726

Maybe you can do this,

PersonTable.Where(p => p.employeeType == 1)
    .Select(p => new Employee { ... })
    .Cast<IPerson>()
    .Concat(
        PersonTable.Where(p => p.employeeType == 2)
            .Select(p => new Manager { ... })
            .Cast<IPerson>());

you could also try,

PersonTable.Select(p => 
     p.employeeType == 1 ? (IPerson)(new Employee { ... }) :
     p.employeeType == n ? (IPerson)(new Other { ... }) :
     (IPerson)(new Manager { ... }));

or, if it didn't need to be IQueryable,

PersonTable.AsEnumerable().Select(p => {
    switch(p.employeeType)
    {
        case 2:
            return Manager { ... };

        default:
            return Employee { ... };   
    }});

Upvotes: 1

Related Questions