locked
How to annotate the EF model to return only a subset of the properties belonging to an entity? RRS feed

  • Question

  • Hi,

    The query below return all the properties from the tblUser entity. However, I only need a subset of the properties on the client. Some of the fields in the DB contain even large xml that I do not need to return to the client.

    The only I need from this entity is the UserId, firstName, LastName and CompanySubscriberId.

    Is there anything I could try to do like annotating the model to hide some properties since I do not want to use stored procedures or views?

    LINQ QUERY

    var users = e.Query.Cast<tblUser>();

        e.Query = from u in users
                  where u.tblWorkgroups
                  .Any(wg => wg.tblUsers.Any())
                  select u;

    I have tried to create a projection by using an anonymous type but it does not work with my ASP.NET EntityDataSource since it expecting a tblUsers set and not an anonymous type:

    e.Query = from u in users
              where u.tblWorkgroups
              .Any(wg => wg.tblUsers.Any())
              select new
              {
                 u.UserId,
                 FullName = u.FirstName + " " + u.LastName,
                 u.FirstName ,
                 u.LastName,
                 u.CompanySubscriberId 
              };

    Furthermore, I have tried casting it to a tblUser but it also does not work.

    e.Query = (from u in users
              where u.tblWorkgroups
              .Any(wg => wg.tblUsers.Any())
              select new
              {
                 u.UserId,
                 FullName = u.FirstName + " " + u.LastName,
                 u.FirstName ,
                 u.LastName
              }).Cast<tblUser>();

    Cheers

    C

     

    Wednesday, February 1, 2012 1:32 PM

Answers

  • ad 1) I'm afraid you'll have to either drop the not-null constraint or add a default (at the database level). A not null constraint at the EF level should be OK.

    ad 2) The tblUser will contain just the basic fields so that query will not contain the UserXML, LockedOut and DateCreated.

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Thursday, February 2, 2012 10:58 AM

All replies

  • Create a constructor in tblUser entity that receive another tblUser entity and fill the fields you want.

    In the query do someting like this

     

    var users = e.Query.Cast<tblUser>();

        e.Query = from u in users
                  where u.tblWorkgroups
                  .Any(wg => wg.tblUsers.Any())
                  select new tblUser(u);

     

    It should work but the entities returned are not tracked by Entity Context.



    Wednesday, February 1, 2012 2:32 PM
  • You can split the table into two separate entities. Then the tblUser (do drop the "tbl"!) will contain only the base properties and will have a lazily loaded property containing a reference to another entity containing the extended data.

     

    See http://thedatafarm.com/blog/data-access/ef-table-splitting-ndash-the-opposite-of-entity-splitting/


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Wednesday, February 1, 2012 3:15 PM
  • Hi Albert,

    Thanks for your  help.

    1 - I had already created a partial tblUser class so that I could add a FullName property to display in the dropdown list like this:

    namespace PermissionsManager.DAL
    {
       
        public partial class tblUser
        {

            public string FullName { get { return FirstName + " " + LastName; } }
        }
    }

    You suggested that I could add a constructor to tblUser entity that receives another tblUser entity and fills the fields you want.

    I am not sure if I get it. Could you please check my code below and let me know if this is what you mean?

    namespace PermissionsManager.DAL
    {
       
        public partial class tblUser
        {

            public tblUser(tblUser user)
            {
                UserId = user.UserId;
                FirstName = user.FirstName;
                LastName = user.LastName;
                CompanySubscriberId = user.CompanySubscriberId;

            }
           
            public string FullName { get { return FirstName + " " + LastName; } }
       
        }
    }

    Then call it

    e.Query = from u in users
                     where u.tblWorkgroups.Any()
                     select new tblUser(u);

    2 - You said:

    "It should work but the entities returned are not tracked by Entity Context."

    Please could you elaborate a bit more on that?

    - Why the entities returned are not tracked by Entity Context?

    - How can I get the Entity Context to track it?

    Cheers

    C



    Wednesday, February 1, 2012 4:37 PM
  •  

    It is the exactly idea i wrote before.

    The problem you have is clasic in architecture design. The entity tblUser is a class used for transfer between DAL layer and ORM and you need an object in order to communicate the visual layer (Asp.Net) and bussiness layer. The object that transfer information from one layer to other are DTOs (Data transfer objects) and should be accesible by any layer. Some architectures use the same entity objects as DTOs and has the disvantage you face. But has the advantage that it is lighty and very productive. In other architecture other classes are build but more code is needed.

    The solution a gave you is for use the same Entity object as DTO. Basically the idea is to create an instance of the entity and put only the information needed given the case and ignore other fields.

    A better idea is to create an statics method that creates the entity given another entity.

    In this case you want to send only name information but what about if you want to send name and age in other page and in 3th page name and orders? I recommend you to do something like this for better code maintenance:

    public partial class tblUser
        {
            //Avoid the constructor
            public static tblUser CreateWithName(tblUser user)
            {
                tblUser newUser = new tblUser();
                newUser.UserId = user.UserId;
                newUser.FirstName = user.FirstName;
                newUser.LastName = user.LastName;
                newUser.CompanySubscriberId = user.CompanySubscriberId;
            return newUser;
            }
            public static tblUser CreateWith...(tblUser user)
            {
                tblUser newUser = new tblUser();
                ....  //Fill data you want to send
                return newUser;
            }
            public string FullName { get { return FirstName + " " + LastName; } }
      
        }

    Then in the query you still can do:
         e.Query = from u in users
                     where u.tblWorkgroups.Any()
                     select tblUser.CreateWithName(u);
    Or the specific data you want.
    I think that this way the code is more maintainable.

    Concerning to EF and tracking entities

    You are creating an entity by yourself; so DataContext doesn't know it exists. Datacontext is not tracking it.
    If you do the same in two steep you will notice what i'm explain you.

    list = (from u in users
                     where u.tblWorkgroups.Any()
                     select tblUser).ToArray(); //load the query data. All this entities are tracked.
    e.Query = from (u in list) select tblUser.CreateWithName(u);  //This is another query agains the tracked entities that creates not tracked entities with only the data to send

    This code does the same

    Another warning about the DTOs entities (Not tracked) Is that you should use them only for pass information, not for use in DataContext Operations.



    Wednesday, February 1, 2012 5:27 PM
  • Hi Albert,

    Wow!! Thanks for the explanation. You are very knowledgeble.

    Just one last question.

    Since I am not going to use the constructor any more, if I want to return all the properties like before so that the DataContext can track the entities then I just have to use my first query, right?

    e.Query = from u in users
                     where u.tblWorkgroups.Any()
                     select u;

    Cheers

    Claudio

    Wednesday, February 1, 2012 6:35 PM
  • Hi jendaperl,

    Thanks for the link.

    If you use the technique explained in the link, will I loose change tracking?

    It seems that this technique is more complex than the one suggested by Albert. However, it is such a pity that we loose change tracking for updates

    Cheers

    C

    Wednesday, February 1, 2012 6:39 PM
  • Hi Albert,

    I tried using the static method as you suggested but got the error below. Could you please check my code below to see if I am doing something wrong?

     Server Error in '/' Application. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.NotSupportedException: LINQ to Entities does not recognize the method 'MARSPermissionsManager.DAL.tblUser CreateForListBox(MARSPermissionsManager.DAL.tblUser)' method, and this method cannot be translated into a store expression.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:

    [NotSupportedException: LINQ to Entities does not recognize the method 'MARSPermissionsManager.DAL.tblUser CreateForListBox(MARSPermissionsManager.DAL.tblUser)' method, and this method cannot be translated into a store expression.]
       System.Web.UI.WebControls.EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +738
       System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +97
       System.Web.UI.WebControls.ListControl.PerformSelect() +34
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
    


    LINQ to Entities does not recognize the method 'MARSPermissionsManager.DAL.tblUser CreateForListBox(MARSPermissionsManager.DAL.tblUser)' method, and this method cannot be translated into a store expression.

     

    This is the code I am using:

    namespace MARSPermissionsManager.DAL
    {
       
        public partial class tblUser
        {

             public static tblUser CreateForListBox(tblUser user)
            {
                var newUser = new tblUser
                        {
                             UserId = user.UserId,
                             FirstName = user.FirstName,
                             LastName = user.LastName,
                             CompanySubscriberId = user.CompanySubscriberId
                         };
                return newUser;
            }
            
            public string FullName { get { return FirstName + " " + LastName; } }
       
        }
    }

    Client code - code behind asp.net page:

    protected void UsersEntityDataSource_QueryCreated(object sender, QueryCreatedEventArgs e)
    {
                var users = e.Query.Cast<tblUser>();

                e.Query = from u in users
                          where u.tblWorkgroups.Any()
                          select tblUser.CreateForListBox(u);
    }

    Cheers

    C

    Wednesday, February 1, 2012 6:51 PM
  • Your are welcome. It is just what i have learned asking in forums (as you) and searching in google.

    Do that in two steeps as i wrote before. The problem is that EF compile the complete query and it can't convert the static method to a sql query. So make the query first (converted to an array to force entity load) and then return the "convert" query agains the tracked entities. It is almost the same in eficiency. Previously i wrote the code.

    An if you want to send the complete object you can.

    Wednesday, February 1, 2012 7:26 PM
  • Hi Albert,

    I have changed to code to do it in two steps but I still get an error. Could you please shed some light?

    Error 1 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<MARSPermissionsManager.DAL.tblUser>' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?) C:\dev\MarsAdminTool\MARSPermissionsManager\Audit\AuditPermissionManager.aspx.cs 64 23 MARSPermissionsManager


    protected void UsersEntityDataSource_QueryCreated(object sender, QueryCreatedEventArgs e)
    {
                var users = e.Query.Cast<tblUser>();

               
                var list = (from u in users
                     where u.tblWorkgroups.Any()
                     select u).ToArray();
               
               
                e.Query = from us in list
                                select tblUser.CreateForListBox(us);         

     

    Cheers

    Claudio

    Wednesday, February 1, 2012 9:22 PM
  • Hi Albert,

    I tried casting it as IQueriable but got another error:

    QueryCreated event returned a query of type 'EnumerableQuery`1' when type 'ObjectQuery`1' is required.

    The EntityDataSource expects the tblUser object I think.

    var users = e.Query.Cast<tblUser>();

    var list = (from u in users
                  where u.tblWorkgroups.Any()
                  select u).ToArray(); 
                
               
                e.Query = (from us in list 
                     select tblUser.CreateForListBox(us)).AsQueryable(); 

    Any ideas?

    Cheers

    C

    Wednesday, February 1, 2012 9:28 PM
  • No you will not loose change tracking. Just the opposite. Unlike the hack proposed by Albert this is actually going to work correctly.

    Albert´s solution has several problems. First, it´s actually very hard to do correctly. Well, it´s impossible to do correctly, but whatever. The thing is that you have to explain what fields you want included in a way LINQ to Entities understands. Otherwise you are still forcing the database to return all the data, including the large XMLs and only then dutifully filter the unwanted data out and create broken semicopies of the objects. Second, the semicopies are broken, they can´t be tracked because as soon as you do you are sure to overwrite the fields you did not copy in the database.

    Table splitting takes a little while to set up, but then your tblUser will contain just the data you wanted and the rest will be available by simple tblUserInstance.Extended.TheNameOfTheXMLField in case you need it. 


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Thursday, February 2, 2012 12:55 AM
  • Hi jendaperl,

    Suppose I split my table like this:

    tblUser
    ----------
    Id     Not Null
    FirstName   Not Null
    LastName   Not Null
    CompanySubscriberID   Not Null
    DateCreated

    tblUserExtraDetail
    ----------------------
    Id      Not null
    UserXML      Not Null
    LockedOut 
    DateCreated

    1 - What happens if you "insert" one entity and the other entity has not-null limitations? In my example above, create a tblUser and have a not-null constraight on UserXML field Not Null in the tblUserExtraDetail table.

    2 - Also could you please help me to understand how I would fetch data from the tblUser with the limited properties to fill a Drop down list after I split the tables as above?

    That is what I was doing before but it return all fileds:

    var users = e.Query.Cast<tblUser>();

    e.Query = from u in users
                     where u.tblWorkgroups.Any()
                     select u;

    Cheers

    C

    Thursday, February 2, 2012 10:46 AM
  • ad 1) I'm afraid you'll have to either drop the not-null constraint or add a default (at the database level). A not null constraint at the EF level should be OK.

    ad 2) The tblUser will contain just the basic fields so that query will not contain the UserXML, LockedOut and DateCreated.

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Thursday, February 2, 2012 10:58 AM
  • Thanks jendaperl
    Thursday, February 2, 2012 12:39 PM
  • Hy Claudio Pallone:

    I think the error is because the ASP.NET way you use to bind data. Maybe ASP.Net expects a query but you are returning an IEnumerable that is not a query. Check this in the view, If so, change the bind to an ObjectData (I have a bit experience with binding in asp.net i don't know if it is the right term).

    Hy jendaperl...

    The advice i described before worked for me. And there are many ways to do that. And it worked fine. According to disvantages; you could configurate the XML field with lazy loading in order to not load automatically, or even read a dynamic class and then fill the shaped data to DTO entities. The DTOs are not tracked but it is not a disvantages; thouse objects are designed to travel across layers in order to modify but not save directly to DB. As I say before sometimes other classes (not the same entities) are created for this purposes. It is a more perfect architecture but heaviest too.

    Thursday, February 2, 2012 1:34 PM
  • Sorry I missed understand you and i wrote about architectural matters (N layers). Currently i'm working with Ajax (ExtJs) and i have to send partial information to the client and this is the way i use. You use ASP.Net and it is not the same. The way to avoid loading heavy properties is using lazy loading in Model editor.
    Thursday, February 2, 2012 3:36 PM
  • Hi Albert,

    Thanks for your explanation.

    I thought that lazy loading was only for naviation properties not fields in the same table.

    Suppose you have User and Workgroup table. One User can belong to many Workgroups. In the User entity you get a navigation property to Workgroups and this is only loaded if you request it.

    But in my case, I am only using the tblUser and do not want the UserXML field and other fields to be sent down to the client.

    How do I change the entity in the Model Editor to do that?

    Cheers

    C  

    Thursday, February 2, 2012 3:44 PM
  • I was reading the EF book and i noticed that EF doesn't support lazy loading in data properties. Was LinqToSql wich support it. It affect the efficiency. The best solution is what jendaperl says. However you can query with shaped data (in order to not load the heavy propery) and seek the way to map the asp.net view with the dynamyc objects returned by the query.  Please remove my post as an answer. I was wrong.

    Thursday, February 2, 2012 5:03 PM
  • ok thanks
    Thursday, February 2, 2012 6:41 PM
  • I was reading and searching an easy solutions for this problem... and i should confess I'm a bit disapointed. I had never faced this case. I will consider it in future. I think that EF team should consider to put a feature that allows mark a single property as lazy loading; just like LinkQToSql does. However you can store the property content in a file and store the file path in the property.
    Tuesday, February 7, 2012 1:52 PM