loading only IDs from database table via Entity Framework 6 code first

Question

I have entity User with properties

int Id {get; set;}

which is set as primary key

and property

   virtual List<Right> Rights {get; set;}

which is a property holding list of user rights, and is lazy loaded whenever it is needed.

Right is also a property :

public class Right
    {
        [Key]
        public int Id {get; set;}

        public RightType RightType { get; set; }
    }

 public enum RightType : byte
    {
        Own,
        Copy,
        Delete
    }

In my code I create a user from the database as usual, and later I want to get only the IDs of the rights so I try it this way :

myUser.Rights.Select(x=>x.Id).ToList();

It get me the collection of IDs I want, but the query going to the database is getting all the columns of Rights table which is not nessesary and in other cases it may be very problematic (imagining loading great amnout of data only to get the IDs :/)

the query looks like:

> "SELECT 
>     [Extent1].[Id] AS [Id], 
>     [Extent1].[RightType] AS [RightType], 
>     [Extent1].[User_Id] AS [User_Id]
>     FROM [dbo].[Rights] AS [Extent1]
>     WHERE ([Extent1].[User_Id] IS NOT NULL) AND ([Extent1].[User_Id] = @EntityKeyValue1)"

Do you have any idea how to load only the IDs ?? any help will be appreciated :)

thank you

Don't use entity framework -- this is why it is slow and prone to performance issues. — Hogan
– Hogan, Commented Nov 19, 2013 at 18:09
Nope, I said, in my opinion entity framework is a bad choice exactly because of issues like this. The EF people will argue over the best way and I won't get involved in that. — Hogan
– Hogan, Commented Nov 19, 2013 at 18:12

Vitaliy Kalinin · Accepted Answer · 2013-11-22 10:24:32Z

2

You can give a try to something like that:

    var ids = _context
.Set<User>()
.Where(u.Id==id)
.SelectMany(u=>u.Rights.Select(r=>r.Id))
.ToList();

Don't tried it against EF, but if put some interfacing, generics and LINQ here following should work:

        public interface IHaveId<T>
        {
            T Id { get; set; }
        }

        public class User : IHaveId<int>
        {
            public int Id { get; set; }
            public virtual List<Right> Rights { get; set; }
        }

        public class Right : IHaveId<int>
        {
            public int Id { get; set; }

            public RightType RightType { get; set; }
        }

        public enum RightType : byte
        {
            Own,
            Copy,
            Delete
        }

        public static IEnumerable<TKey> GetChildIds<TParent,TChild,TKey>(IQueryable<TParent> src, TKey parentId, Expression<Func<TParent,IEnumerable<TChild>>> childSelector)
            where TParent : IHaveId<TKey>
            where TChild : IHaveId<TKey>
            where TKey : struct, IEquatable<TKey>

        {
            var result = src
                .Where(parent => parentId.Equals(parent.Id))
                .SelectMany(childSelector)
                .Select(child => child.Id);

            return result;
        }
//sample usage
//var ids = GetChildIds<User,Right,int>(_context.DbSet<User>(), user=>user.Rights).ToList();

edited Nov 22, 2013 at 10:24

answered Nov 19, 2013 at 19:17

Vitaliy Kalinin

1,86913 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Smith Over a year ago

and cany ou please give me a hand on how to make a method from this query - a method that can get Ids for any property of User entity and the property would be a input parameter of the method. If i put as input method a property, first the hold property (in this case Rights) is loaded and then the Ids are loaded, which is not exactly what I want only the Ids to be loaded

Vitaliy Kalinin Over a year ago

Signature for this method would be a good starting point. Also I am not sure, but maybe it would better be to make new questions, so that to give others possibility to jump in.

John Smith Over a year ago

Thanks for answer :) Unfortunately this construction seems not to work : Unable to cast the type 'Entities.Person' to type 'Entities.IHaveId`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. LINQ to Entities only supports casting EDM primitive or enumeration types.

John Smith Over a year ago

in User entity : public List<int> GetUserRightsIds(Context ctx) { return GetChildIds<User, Right, int>(ctx.Set<User>(),Id,x=>x.Rights).ToList(); }

Collectives™ on Stack Overflow

loading only IDs from database table via Entity Framework 6 code first

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related