none
What is the best practice, to select a table, which has more than 150 columns using EF? RRS feed

  • Question

  •        

    What is the best practice, to select a table, which has more than 150 columns using EF. We have tables in our legacy application, where some tables have more than 120 columns. We are planning to convert legacy application in to C# with EF on the top of our existing db. We can't go for new db design, because it will require data migration and testing. 

    In my understanding below points may help. 

    a. Remove unnecessary properties from designer or class.

       Not a good solution, in future I may need other properties as well.


    b. Split the entity into multiple entities

    OK, but understanding all columns in the db is not easy. because it is very old system. For development, developer doesn't require to know all columns, and some of the columns could be obsolete.

    c. Don’t select entity, instead specify the columns in the select


    Below statement retrieves 147 columns, but I need only few columns (ShipName, Code). So can't use this.

       var query = from c in ctx.VoyageAccounts where c.STATUS == "a" select c;



    Use DTO, this will also solve the problem. Below query will generate query only for 2 columns. But this can be used only for one to one (VoyageAccounts --> VoyageAccountsDTO).  Can't load children of VoyageAccounts along with this.

     var query = from x in ctx.VoyageAccounts
      where x.BALLAST_LEG == 1
      select (new VoyageAccountsDTO{ value = x.SHIP, value1 = x.SHIP_CODE });

    any better approach or best practice.        

    regards,

    Anand

       

    Regards, Anand

    Friday, October 12, 2012 5:43 AM

Answers

  • Hi Anand,

    Welcome to the MSDN forum.

    You can use DTO classes to receive the data along with its father. Please check this (a simple sample):

    Class definition:

        public class Princess
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    
        public class Unicorn
        {
            public int Id { get; set; }
            public string Name { get; set; }
    
            [Timestamp]
            public byte[] Version { get; set; }
    
            public int PrincessId { get; set; } // FK for Princess reference
            public virtual Princess Princess { get; set; }
        }
    
        public class UnicornDTO
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public byte[] Version { get; set; }
    
            public int PrincessId { get; set; }
            public PrincessDTO Princess { get; set; }
        }
    
        public class PrincessDTO
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    

    Query:

    var query = (from u in context.Unicorns
                                where u.Name == "Silly"
                                select new UnicornDTO { Id = u.Id, Name = u.Name, Princess = new PrincessDTO { Id = u.Princess.Id, Name = u.Princess.Name }, PrincessId = u.PrincessId, Version = u.Version }).First();
    

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Tuesday, October 23, 2012 8:45 AM
    Monday, October 15, 2012 8:03 AM