Multi key join with Web API OData

Question

I am new to Web API Odata, while building a controller that has a join on multiple keys, it doesn't seem to produce correct Sql Statement.

i must mention that using Entity Framework alone in a Console App produces expected Sql.

here is my Controller

public class Vw_SearchEntityController : ODataController
{        
    private CatalogDb db;
    public Vw_SearchEntityController()
    {
        this.db = new CatalogDb();
    }


    [Queryable]
    public IQueryable<Vw_SearchEntity> Get()
    {
        var result = db.Vw_SearchEntity.Join(db.Vw_EntityType,
            o => new { TypeName = o.ClassType, Key = o.EntityId },
            i => new { TypeName = i.EntityTypeName, Key = i.EnumName },
            (s, i) => s);
        return result.AsQueryable();            
    }
}

The sql it generates is

SELECT ...
FROM  (SELECT 
...) AS [Extent1]
INNER JOIN (SELECT ... FROM [Tenant].[Vw_EntityType] AS [Vw_EntityType]) AS [Extent2]
ON 1=0

As you can see the ON clause is not being generated. If i modify the linq statement to join on single key, it works fine. Is this is a bug with OData provider ?

The versions of various packages used are

EntityFramework 6.1.0-beta1
Microsoft ASP.NET Web API 2.1 Odata Version 5.1.1
.Net 4.5 VS2012

Is there a reason you are joining manually? Is there no relationship defined between the entities that you can take advantage of? — Dismissile
this is a simplified version of the problem, but no there is no formal relationship between these entities — np-hard

Feng Zhao Feng Zhao · Accepted Answer · 2014-03-03T01:59:20

I think it is an issue in EntityFramework 6.1.0-beta1 and will be fixed in 6.1 release. https://entityframework.codeplex.com/workitem/2093

Multi key join with Web API OData

1 Answers