none
LargeTable in MVC Entity Model RRS feed

  • Question

  • Hi All,

    Is it possible to set a where clause on an entity model and only expose certain columns when building an entity model in MVC from the database?

    If not, how do you limit the records being returned from the database?  I noticed a filter property but, to me, filter means it will load all records first and then filter them.  I don't want to filter millions of records.

    I am very new to MVC so I apologize if this is easy to answer.

    Thanks

    Thursday, June 29, 2017 8:36 PM

Answers

  • have you considered adding a view to the db?

    you can also select null into the result entity

    e.g. from p in db.product select new Product{ Id=p.Id, Name=null};

    but the result sql is less efficient. 



    Visual C++ MVP

    • Marked as answer by Phill D Friday, June 30, 2017 12:01 AM
    Thursday, June 29, 2017 10:56 PM

All replies

  • have you considered adding a view to the db?

    you can also select null into the result entity

    e.g. from p in db.product select new Product{ Id=p.Id, Name=null};

    but the result sql is less efficient. 



    Visual C++ MVP

    • Marked as answer by Phill D Friday, June 30, 2017 12:01 AM
    Thursday, June 29, 2017 10:56 PM
  • I hadn't thought of that however, due to very poor design of the database I'm working with, I need to perform some complex case statements and possibly perform a union too so, I am going to use a stored procedure with a parameter to return the dataset instead of pulling directly from the entity table.
    Friday, June 30, 2017 12:01 AM
  • Hi Phill D,

    >>Is it possible to set a where clause on an entity model and only expose certain columns when building an entity model in MVC from the database?

    Yes, you could expose certain columns by using DTO. like this:

     using (var db = new EFDemoContextDemo())
    {
         var query = from p in db.Agents
                                where p.AgentId > 0
                                select new AgentDto
                                {
                                    AgentId = p.AgentId,
                                    FirstName = p.FirstName
                                };
    }

    #Models

    public partial class Agent
        {
            public int AgentId { get; set; }
    
            public string FirstName { get; set; }
    
            public string Surname { get; set; }
    
            public int OfficeId { get; set; }
        }

    #Dtos

     public class AgentDto
        {
            public int AgentId { get; set; }
            public string FirstName { get; set; }
        }

    For more information, please refer to:

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 30, 2017 5:44 AM
    Moderator