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.
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
.