none
LINQ to Entity Interfaces RRS feed

  • Question

  • I'd like to be able to do queries against entity interfaces as opposed to actual instances of entities.

    I have database first Entities inheriting EntityObject and an interface for the entity.  So I have a UserEntity generated inheriting EntityObject  and IUserEntity interface as well as a UserRoleEntity inheriting EntityObject and IUserRoleEntity interface. 

    My interfaces inherit the same interfaces inherited by EntityObject. ie. IEntityWithKey, IEntityWithChangeTracker, IEntityWithRelationships

    I have repositories which return a IQueryable<UserEntity> cast to a IQueryable<IUserEntity>

    The follow code works...

                IQueryable<IUserEntity> uq = _userRepository.Select();
                IQueryable<IUserRoleEntity> urq = userRoleRepository.Select();


                IQueryable<UserEntity> uqe = (IQueryable<UserEntity>)uq;
                IQueryable<UserRoleEntity> urqe = (IQueryable<UserRoleEntity>)urq;

                IQueryable<UserEntity> users =
                    from user in uqe
                    from userRole in urqe
                    where
                        user.ID == userRole.UserID &&
                        userRole.RoleID == 10
                    select user;

                List<UserEntity> userList = users.ToList();
                Assert.IsTrue(userList.Count > 0);

    However the code below does not work throwing a NotSupportedException on ToList()

    "LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[MyCompany.Entities.IUserRoleEntity] Select()' method, and this method cannot be translated into a store expression."

                IQueryable<IUserEntity> users2 =
                    from user in uq
                    from userRole in urq
                    where
                        user.ID == userRole.UserID &&
                        userRole.RoleID == 10
                    select user;

                List<IUserEntity> userList2 = users2.ToList();
                Assert.IsTrue(userList2.Count > 0);

    Basically I'm forced to write LINQ queries against classes and can only cast to my interfaces at a point where LINQ has generated the SQL for the query.

    I know I'm going to get the inevitable why do you want to do this?  It should be obvious but.... I want to be able to share common code across several different implementations of a system.  E.g for A,B,C implementations I might use IUserEntity and for implementations D,E and F use IDEFUserEntity.  I'd like to use interfaces instead of classes as I'd like to apply these interfaces to different Entity Models.  In summary typical Enterprise app development.


    Mick Lang



    • Edited by Mick Lang Thursday, August 16, 2012 10:04 AM Remove company
    Thursday, August 16, 2012 10:02 AM

All replies

  • Could you pose the code about repository and interface here?

    Go go Doraemon!

    Friday, August 17, 2012 2:44 AM
  • Well I have a lot of code, I simply can't possibly post it all here. I doubt this is going to help...

    What I actually had is a little more complicated. But to reproduce the problem do something like this...

    Interfaces

        public partial interface IUserRoleEntity : IMyEntity
        {
    	int UserID { get; set; }
    	int RoleID { get; set; }
        }
        public partial interface IUserEntity : IMyEntity
        {
    	string Surname { get; set; }
        }
        public interface IMyEntity : IEntityWithKey, IEntityWithChangeTracker, IEntityWithRelationships
        {
            int ID { get; }
    
            string Status { get; set; }
        }

    Entity....

        [EdmEntityTypeAttribute(NamespaceName="MyCompany.Data.Models", Name="UserEntity")]
        [Serializable()]
        [DataContractAttribute(IsReference=true)]
        public partial class UserEntity : EntityObject, IUserEntity
        {
    .......

    Repository...

    public class UserRepository : GenericRepository<IUserEntity, UserEntity>, IUserRepository {

    ...

    Generic Repository...

    public class GenericRepository<TEntityInterface, TEntity> : IGenericRepository<TEntityInterface> where TEntityInterface : IMyEntity where TEntity : EntityObject, IMyEntity {         private DbContext _context;

    .... public IQueryable<TEntityInterface> Select() { return (IQueryable<TEntityInterface>)_context.Set<TEntity>().AsQueryable<TEntity>(); } ... }

    An update with entity framework 5 I get a different error...

    Unable to cast the type 'System.Data.Objects.ObjectQuery`1' to type 'System.Linq.IQueryable`1'. LINQ to Entities only supports casting Entity Data Model primitive types.

    Seems pretty straight forward.  Queries only work with TEntity but not with TEntityInterface.

    I've already started refactoring my code such that my repositories still return Interfaces, however they only cast the results of queries at a point after the SQL query has been generated by EF.  Thank god for collection covariance.

    Basically this means all the code using my repositories will be reuseable between implementations, however the LINQ queries within my repositories are not.  The original design was to use a proxy pattern to enable repositories to be written with all queries written against interfaces not entity classes.


    Mick Lang



    • Edited by Mick Lang Friday, August 17, 2012 8:17 AM
    Friday, August 17, 2012 7:49 AM
  • I'm trying to repro the issue, but I can't, could you please upload a demo to SkyDrive?

    Go go Doraemon!

    Monday, August 20, 2012 2:28 AM
  • Hi Mick Lang,

    This exception means some methods in your query couldn't to be translated to T-SQL, do you have any own methods in the 'Select'?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, August 20, 2012 2:40 AM
    Moderator
  • Hey there,

    I will have to construct a smaller project reproducing the issue, I can't upload the code as is.  I'll have to do this out of working hours.  I'll post the URL when I've created the project.


    Mick Lang

    Tuesday, August 21, 2012 2:03 AM
  • Hi Allen,

    No, I'm 99% certain that there are no methods being called in the LINQ query causing the exception.  I have two queries which are identical, except one is using interfaces and the other Entity Model Primative types. I'll repeat the code I posted above, you can clearly see I'm actually using the exact same instances and getting different results depending on how the instance is cast....

                IQueryable<IUserEntity> uqInterfaces = _userRepository.SelectInterface();
                IQueryable<IUserRoleEntity> urqInterfaces = userRoleRepository.SelectInterface();
    
                IQueryable<UserEntity> uqEntities = (IQueryable<UserEntity>)uqInterfaces;            // Cast to the Entity Model Primative Type 
                IQueryable<UserRoleEntity> urqEntities = (IQueryable<UserRoleEntity>)urqInterfaces;  // Cast to the Entity Model Primative Type 
    
                IQueryable<UserEntity> users =
                    from user in uqEntities
                    from userRole in urqEntities
                    where
                        user.ID == userRole.UserID &&
                        userRole.RoleID == 10
                    select user;
    
                List<UserEntity> userList = users.ToList();
                Assert.IsTrue(userList.Count > 0);
    
                List<IUserEntity> userList2 = users.ToList<IUserEntity>();
                Assert.IsTrue(userList2.Count > 0);
    
                IList<IUserEntity> userlist4 = uqInterfaces.AsEnumerable().ToList();
                Assert.IsTrue(userlist4.Count > 0);
    
                IQueryable<IUserEntity> users2 =
                    from user in uqInterfaces
                    from userRole in urqInterfaces
                    where
                        user.ID == userRole.UserID &&
                        userRole.RoleID == 10
                    select user;
    
                List<IUserEntity> userList3 = users2.ToList();  // Throws Exception listed below
                Assert.IsTrue(userList3.Count > 0);

    System.NotSupportedException was unhandled by user code
      Message=Unable to cast the type 'System.Data.Objects.ObjectQuery`1' to type 'System.Linq.IQueryable`1'. LINQ to Entities only supports casting Entity Data Model primitive types.
      Source=System.Data.Entity
      StackTrace:
           at System.Data.Objects.ELinq.ExpressionConverter.ValidateAndAdjustCastTypes(TypeUsage toType, TypeUsage fromType, Type toClrType, Type fromClrType)
           at System.Data.Objects.ELinq.ExpressionConverter.GetCastTargetType(TypeUsage fromType, Type toClrType, Type fromClrType, Boolean preserveCastForDateTime)
           at System.Data.Objects.ELinq.ExpressionConverter.CreateCastExpression(DbExpression source, Type toClrType, Type fromClrType)
           at System.Data.Objects.ELinq.ExpressionConverter.ConvertTranslator.TranslateUnary(ExpressionConverter parent, UnaryExpression unary, DbExpression operand)
           at System.Data.Objects.ELinq.ExpressionConverter.UnaryTranslator.TypedTranslate(ExpressionConverter parent, UnaryExpression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
           at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectManyTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
           at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
           at System.Data.Objects.ELinq.ExpressionConverter.Convert()
           at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
           at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
           at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
           at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
           at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
           at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
           at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
           at MyCompany.Data.Test.UsersCustom.GetUserRoles() in D:\Projects\Test\UsersCustom.cs:line 105
      InnerException:

    Mick Lang


    • Edited by Mick Lang Tuesday, August 21, 2012 2:41 AM
    • Proposed as answer by Fahd Harb Tuesday, December 30, 2014 5:48 PM
    Tuesday, August 21, 2012 2:21 AM
  • I think this is the same problem as here...

    http://stackoverflow.com/questions/1361085/cast-linq-results-to-listmyinterface

    and here...

    http://stackoverflow.com/questions/1145847/entity-framework-linq-to-entities-only-supports-casting-entity-data-model-primi

    The solution here is to call AsEnumerable before attempting to cast to an interface, at which point EF executes an SQL query.  LINQ operations following a call to AsEnumerable are in memory operations.  It's interesting that in the code above...

                IList<IUserEntity> userlist4 = uqInterfaces.AsEnumerable().ToList();
               
    Assert.IsTrue(userlist4.Count > 0);

    ...executes successfully, its only when I attempt a more complex operation involving a join that it fails.


    Mick Lang

    Tuesday, August 21, 2012 2:37 AM
  • Hi Mick Lang,

    Yes, if you call ToList method, the query will be translated to T-SQL and executed in the database, but custom type conversion couldn't be executed in the database, so it failed. If you call AsEnumerable method, what you really do is Linq to Object, not Linq to Entities, the type conversion occurs in the client, so it could  success. : )

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, August 22, 2012 2:13 AM
    Moderator
  • Hi Allen,

    I monitored the SQL being produced using SQL profiler.  Indeed you do get a result when using IEnumerable<IEntityInterface> however the SQL statements that were produced were far from optimal.  

    Using IQuerable and Entity Data Model primitive types i.e.

                IQueryable<UserEntity> users =
                   
    from user in uqEntities
                   
    from userRole in urqEntities where
                        user
    .ID == userRole.UserID &&
                        userRole
    .RoleID == 10
                   
    select user;

    you get a query that looks like...

    SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Location] AS [Location], 
    [Extent1].[Status] AS [Status], 
    [Extent1].[CreatedDate] AS [CreatedDate], 
    [Extent1].[CreatedBy] AS [CreatedBy], 
    [Extent1].[ModifiedDate] AS [ModifiedDate], 
    [Extent1].[ModifiedBy] AS [ModifiedBy], 
    [Extent1].[ClosedDate] AS [ClosedDate], 
    [Extent1].[ClosedBy] AS [ClosedBy], 
    FROM  [dbo].[tblUser] AS [Extent1]
    INNER JOIN [dbo].[tblUser_Role_rel] AS [Extent2] ON [Extent1].[ID] = [Extent2].[UserID]
    WHERE 10 = [Extent2].[RoleID]

    However the following code....

    	IEnumerable<IUserEntity> uenum = uqInterfaces.AsEnumerable();
    	IEnumerable<IUserRoleEntity> urenum = urqInterfaces.AsEnumerable();
    	IEnumerable<IUserEntity> users2 =
    		from user in uenum
    		from userRole in urenum
    		where
    			user.ID == userRole.UserID &&
    			userRole.RoleID == 10
    		select user;
    	List<IUserEntity> userList3 = users2.ToList();
    	Assert.IsTrue(userList3.Count > 0);

    Resulted in 1024 select statements on the User Role table (twice the number of rows in the user table)

    I also tried...

                IEnumerable<IUserEntity> users2 =                 from user in uenum                 join userRole in urenum                 on user.ID equals userRole.UserID                  where                                         userRole.RoleID == 10                 select user; With this the following SQL is generated...

    SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[UserID] AS [UserID], 
    [Extent1].[RoleID] AS [RoleID], 
    [Extent1].[LocationID] AS [LocationID], 
    [Extent1].[Status] AS [Status], 
    [Extent1].[CreatedDate] AS [CreatedDate], 
    [Extent1].[CreatedBy] AS [CreatedBy], 
    [Extent1].[ModifiedDate] AS [ModifiedDate], 
    [Extent1].[ModifiedBy] AS [ModifiedBy], 
    [Extent1].[ClosedDate] AS [ClosedDate], 
    [Extent1].[ClosedBy] AS [ClosedBy]
    FROM [dbo].[tblUser_Role_rel] AS [Extent1]
    -- THEN
    SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Location] AS [Location], 
    [Extent1].[CreatedDate] AS [CreatedDate], 
    [Extent1].[CreatedBy] AS [CreatedBy], 
    [Extent1].[ModifiedDate] AS [ModifiedDate], 
    [Extent1].[ModifiedBy] AS [ModifiedBy], 
    [Extent1].[ClosedDate] AS [ClosedDate], 
    [Extent1].[ClosedBy] AS [ClosedBy], 
    FROM [dbo].[tblUser] AS [Extent1]

    Quite clearly its getting all the rows from both tables then doing an in memory join. LINQ to objects doesn't seem to behave very well when doing joins.

    I'm thinking only the Entity Framework is a viable option for doing complicated queries.

    M


    Mick Lang


    • Edited by Mick Lang Thursday, August 23, 2012 12:33 AM
    Thursday, August 23, 2012 12:30 AM
  • Hi Mick Lang,

    When we do complicated query, commonly, we will write a stored procedure in the database and call it directly in Entity Framework. Linq to Entities also will not been translated to T-SQL as optimal, so if the query is too complicated, stored procedure is a better way for performance.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 23, 2012 2:56 AM
    Moderator
  • Hi Allen,

    I think it was always envisioned we we would still need to use stored procs but for VERY complicated queries.

    I do not think query with a single join between two tables classifies as a VERY complicated query, and it shouldn't warrant the creation of a stored proc. It should be something that can be done in code using LINQ. It can but only using "Entity Data Model primitive types" i.e. the classes generated for you by EF.

    The other path is to go with POCO and code first, I can't say I'm a fan of that approach either as, control over the database schema should be in the hands of DB administrators not software developers.

    If Microsoft is serious about the Entity Framework for use in Enterprise applications it really needs to steer users down a better path than forcing the use of tightly coupled classes. Loose coupling is very hard to do in .NET without using interfaces.

    A LINQ query should be able to function against an interface when the underlying data type is an EntityObject associated with all the required database meta data to generate the SQL query. Perhaps you need to give us attributes which we can apply to interfaces to help EF associate database meta data with the entity interface.

    Cheers


    Mick Lang


    • Edited by Mick Lang Thursday, August 23, 2012 8:48 AM
    Thursday, August 23, 2012 8:42 AM
  • Now, EF doesn't support that, may be one day, it will. EF has opened source, this gives us a lot of space to customize. Now, you can submit your good idea here. You can mark your own reply as answer to help other community members.

    Go go Doraemon!


    Monday, August 27, 2012 2:12 AM