Linq result from many tables into an inherit class with nested classes

Question

Linq result from many tables into an inherit class with nested classes

c# entity-framework-6 linq nested

Question

I've got an SQL table but the customer removed all the foreign keys and won't let me rebuild table with all the foreign keys.

I've used Entity Framework on a database first approach, I've got something like :

public class MainTable
{
    MainTableID,
    TableChildOneID,
    TableChildTwoID,
    //... many properties
}

public class TableChildOne
{
    TableChildOneID,
    //... many properties
}

public class TableChildTwo
{
    TableChildTwoID,
    //... many properties
}

I've created a class which represents the "real" linked nested class like :

public class MainTable_Extended : MainTable
{
    public TableChildOne ChildOne { get; set; }
    public TableChildTwo ChildTwo { get; set; }
}

Note that the MainTable contains a lot of properties

Here's the query where I'm looking for a clean syntax

IQueryable<MainTable_Extended> listMainTable =
            from main in db.MainTable
            from childone in db.TableChildOne.Where(childone => childone.TableChildOneID == main.TableChildOneID).DefaultIfEmpty()
            from childtwo in db.TableChildTwo.Where(childtwo => childtwo.TableChildTwoID == main.TableChildTwoID).DefaultIfEmpty()
            select new MainTable_Extended
            {
                ChildOne = childone ,
                ChildTwo = childtwo ,
                All others properties coming from => main 
            };

How do I populate my class MainTable_Extended without doing it by hand for all properties.

Hassan N

View more on Stack Overflow

Darem · Accepted Answer · 5/29/2018 12:59:07 PM

Maybe this works for you?

public class MainTable
   {
       public int MainTableID;
       public int TableChildOneID;
       public int TableChildTwoID;
       //... many properties
   }

public class TableChildOne
   {
       public int TableChildOneID;
       //... many properties
   }

public class TableChildTwo
   {
       public int TableChildTwoID;
       //... many properties
   }

public class MainTable_Extended
   {
       public TableChildOne ChildOne { get; set; }
       public TableChildTwo ChildTwo { get; set; }
       public MainTable Main { get; set; }
   }

public class DB : DbContext
   {
       public DB()
           : base("name=YourName")
       {}

       public virtual DbSet<MainTable> MainTables { get; set; }
       public virtual DbSet<TableChildOne> TableChildOnes { get; set; }
       public virtual DbSet<TableChildTwo> TableChildTwos { get; set; }
   }

public class Test
   {
      public void TestTest()
        {
           var db = new DB();

           IQueryable<MainTable_Extended> listMainTable =
           from main in db.MainTables
           from childone in db.TableChildOnes.Where(childone => childone.TableChildOneID == main.TableChildOneID).DefaultIfEmpty()
           from childtwo in db.TableChildTwos.Where(childtwo => childtwo.TableChildTwoID == main.TableChildTwoID).DefaultIfEmpty()
           select new MainTable_Extended
            {
               ChildOne = childone,
               ChildTwo = childtwo,
               Main = main
            };
        }
    }

I let the MainTable_Extended class not inhier from the MainTable class. Instead i make a property with a MainTable object. So in the query i give the MainTable_Extended a value for the property.

So no you can query the propertys of MainTable by call the property Main of MainTable_Extended.

Linq result from many tables into an inherit class with nested classes

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related