Return different objects with one LINQtoSQL statement

Question

Here's my problem: I have an IPerson which is implemented by Employee and Student. What I really want is what you see below. One LINQ statement to get each type of IPerson. This works great until I call the method ;). It makes sense as to why I'd get the error, but I am really struggling as to find a decent way to pull all IPerson objects from the DB and avoid putting switch statements all over my application.

public IQueryable<IPerson> getPersons() {

        // gives Types in Union or Concat have different members assigned error

        var people = from p in db.Persons select p;

        var students = (from s in people
                        where s.TypeId == (int)PersonType.Student
                        select new Student
                        {
                            Id = s.Id,
                            Age = s.Age.GetValueOrDefault(0),
                            Name = s.Name,
                            Major = s.Student.Major ?? "None",
                            CreditHours = s.Student.CreditHours.GetValueOrDefault(0),
                            PersonType = (PersonType)s.TypeId
                        }).Cast<IPerson>();
        var employees = (from e in people
                        where e.TypeId == (int)PersonType.Employee
                        select new Employee
                        {
                            Id = e.Id,
                            Age = e.Age.GetValueOrDefault(0),
                            Name = e.Name,
                            PersonType = (PersonType)e.TypeId,
                            Salary = e.Employee.Salary.GetValueOrDefault(0)
                        }).Cast<IPerson>();

        return students.Concat<IPerson>(employees);
        //return (students.ToList()).Concat<IPerson>(employees.Cast<IPerson>().ToList()).AsQueryable<IPerson>();
    }

Above, there is a commented out return statement - that essentially does a .ToList() and forgoes the whole deferred execution thing, creating 2 SQL statements - not ideal.

Any help is appreciated!

Answer 1

How about this:

public IQueryable<IPerson> getPersons() {

    // gives Types in Union or Concat have different members assigned error

    var people = from p in db.Persons select p;

    return (from s in people
                    where s.TypeId == (int)PersonType.Student
                    select new Student
                    {
                        Id = s.Id,
                        Age = s.Age.GetValueOrDefault(0),
                        Name = s.Name,
                        Major = s.Student.Major ?? "None",
                        CreditHours = s.Student.CreditHours.GetValueOrDefault(0),
                        PersonType = (PersonType)s.TypeId
                    }).Cast<IPerson>().Union((from e in people
                        where e.TypeId == (int)PersonType.Employee
                        select new Employee
                        {
                            Id = e.Id,
                            Age = e.Age.GetValueOrDefault(0),
                            Name = e.Name,
                            PersonType = (PersonType)e.TypeId,
                            Salary = e.Employee.Salary.GetValueOrDefault(0)
                        }).Cast<IPerson>());
}

It's not much better, but you get it it one call. Or, what I would do is something like this:

public IPerson GetPerson(Person p) //I'm guessing that the objects in collection db.Persons is of type Person
{
    IPerson ret;
    switch(p.TypeId)
    {
        case (int)PersonType.Student: ret = .......break;
        case (int)PersonType.Employee: ret = ......break;
    }
    return ret;
}

public IQueryable<IPerson> getPersons() {
    return (from p in db.Persons select p).ToList().Select(p => GetPerson(p)).AsQueryable();
}

But then again you get the switch-statement. Also, if you don't like to do a ToList on the db (if I recall correctly LinqToSQL doesn't support functions using constructors with variables) you can try adding the method GetPerson (I'dd probably rename it though) to the Person class generated by LinqToSQL (partial class), but I'm not certain this is legal.

But how you're going to use the IQueryable coming from getPersons without using switch, I don't know.

Answer 2

Here is what I ended up doing:

Repository

public IQueryable<Database.Person> getDbPersons() {
    return from p in db.Persons select p;
}

// Called by Service layer when viewing all People
public IQueryable<Person> getPersons() {
    return from p in getDbPersons() select new Person { //yada yada };
}

Service Layer

public IList<Person> getPersons() {
    return from p in repository.getPersons() return p;
}

public IPerson getPerson(int id) {
    return repository.getDbPersons().withPersonId(id);
}

// Person Filter Class
public static class PersonFilters
{
    public static IPerson WithPersonId(this IQueryable<SqlServer.Person> qry, int Id)
    {
        return (from p in qry
                where p.Id == Id
                select p).Select(p => ThisPerson(p)).SingleOrDefault();
    }

    private static IPerson ThisPerson(OneToOne.Data.SqlServer.Person x)
    {
        IPerson ret;
        switch (x.TypeId)
        {
            case (int)PersonType.Employee:
                var e = new Employee();
                e.Id = x.Id;
                e.Name = x.Name;
                e.Age = x.Age.GetValueOrDefault(0);
                e.Salary = x.Employee.Salary.GetValueOrDefault(0);
                e.PersonType = PersonType.Employee;
                ret = e;
                break;
            case (int)PersonType.Student:
                var s = new Student();
                s.Id = x.Id;
                s.Name = x.Name;
                s.Age = x.Age.GetValueOrDefault(0);
                s.Major = x.Student.Major;
                s.PersonType = PersonType.Employee;
                ret = s;
                break;
            default:
                throw new Exception("Bad Person Type");
        }
        return ret;
    }
}

Thanks again, Alxandr for pointing me in the right direction!