CODEWITHSUNDEEP
c#sqllinqjoin
erictrigo
erictrigo

Reputation: 1017

Join using LINQ between two tables, return records from only one of them

Here's my code:

public IEnumerable<IUIUPCItemShort> GetActiveWithDefaultCost()
    {
        var query = from upc in DataManagerFactory.Get().Manage<IUPCItem>().GetAll()
                    join inv in DataManagerFactory.Get().Manage<IInventory>().GetAll() on upc.UPCID equals inv.UPCID
                    where inv.ExitDate == null
                         && upc.UnitCost == null
                    select upc;
        return
            query.Cast<IUIUPCItemShort>().ToList();

Basically, I'm trying to do a Join between two tables, UPC and Inventory, and would like to have a list of only UPC that satisfy the WHERE conditions since I only want to show the user a list of UPC. I obviously am doing something really wrong, because I get this message:

Unable to cast object of type 'System.Linq.Expressions.MethodCallExpressionN' to type 'SubSonic.Linq.Structure.ProjectionExpression'.

I assume the problem is in my Linq code, especifically in the select upc; How can I do what I'm trying to accomplish?

Thanks.

Upvotes: 1

Views: 1404

Answers (1)

Servy
Servy

Reputation: 203848

Your query is a query of IUPCItem items, not of IUIUPCItemShort items. It would appear that they query provider doesn't know how to translate the Cast operation for that conversion.

If you know that IUPCItem in your C# code is convertible to a IUIUPCItemShort then iyou probably just want to avoid having the query provider try to process the Cast. Ensure that it's done in Linq to Objects like so:

return query.AsEnumerable().
    Cast<IUIUPCItemShort>()
    .ToList();

If that doesn't work then it means the type really isn't implicitly convertible; you need to determine how to properly get an instance of IUIUPCItemShort from a IUPCItem, possibly creating a method to handle the conversion that you call using Select.

Upvotes: 3

Related Questions