CODEWITHSUNDEEP
.netentity-frameworklinq-to-sql
SzilardD
SzilardD

Reputation: 1751

Returning multiple entities from LinqToSQL (or EF)

Is it possible to return multiple entities from a LinqToSql (or EF) query (the query is inside a method) so that the results will still be composable ?

Something like this:

public IQueryable<KeyValuePair<Customer, Product>> GetCustomerEntities()
{
    return
    (
        from    customer in this.Context.Customers
                join
                product in this.Context.Products on customer.ID equals product.CustomerID       
        select  new KeyValuePair<Customer, Product>(customer, product)
    );
}

Then I want to use the result of this method to further compose the query like:

this.GetCustomerEntities().Where(e => e.Key.Name == "my customer")

The method above compiles but it can't be executed by LinqToSql because it can't convert KeyValuePair to SQL, which is the expected behavior.

Is it possible to achieve this somehow ?

Upvotes: 1

Views: 113

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062725

The problem here is that the runtime can't see that passing customer into the constructor parameter is the same thing as looking at e.Key, since those two things are not obviously the same. You might try creating your own POCO type with getter and setter, i.e.

select new CustomerProduct { Customer = customer, Product = product }

and using that instead of KeyValuePair<Customer,Product>.

Inside a single method, an anonymous type would be the obvious choice.

Upvotes: 1

Bryan Walker
Bryan Walker

Reputation: 921

this.GetCustomerEntities().ToList().Where(e => e.Key.Name == "my customer")

That should work. It's not the most efficient thing possible, but it'll at least run. Once you enumerate it out (with ToList()), the queryable will execute on the SQL server, and any further operations will be on the enumeration in memory. That allows you to use the KeyValuePair class freely.

If you want to further compose the query specifically to take advantage of SQL, then you should probably parameterize your function, and only get the rows you want from the database in the first place.

Upvotes: 0

Related Questions