Almoust all we have done in previous posts can be implemented in LINQ to SQL. Now I'm gonna show you how to create multitable entity, which is imposible in LINQ to SQL.
Suppose we have the following two tables.
We want unite those table into a single entity called SalesOrder. This is a complex mapping, so we have to use certain class to define the mapping. I called this class entity schema class. Since we have two tables, the entity schema class should implement IMultiTableObjectSchema interface. But first let's start from SalesOrder entity.
[Entity(typeof(EntitySchema), "1")]
public class SalesOrder
{
public int SalesOrderDetailID { get; set; }
public short OrderQty { get; set; }
public decimal LineTotal { get; set; }
//SalesOrderHeader fields
public DateTime OrderDate { get; set; }
public exam1sharp.Sales.SalesTerritory Territory { get; set; }
public exam1sharp.Sales.SalesPerson Person { get; set; }
public static QueryCmd Query
{
get
{
return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
.From(typeof(SalesOrder))
.Select(typeof(SalesOrder));
}
}
}
As you can see none of the property has any attribute so this is complete POCO. All mapping data aggregated in entity schema.
SalesOrderHeader table has references to SalesTerritory and SalesPerson, so we add them in class. They are from Sales namespace.
public class EntitySchema : IMultiTableObjectSchema
{
//define tables
private SourceFragment[] _tables = new SourceFragment[]{
new SourceFragment("Sales","SalesOrderHeader"),
new SourceFragment("Sales","SalesOrderDetail")
};
//define join
public Worm.Criteria.Joins.QueryJoin GetJoins(SourceFragment left, SourceFragment right)
{
return JCtor.join(right).on(left, "SalesOrderID").eq(right, "SalesOrderID");
}
//define mapping
public Worm.Collections.IndexedCollection<string, MapField2Column> GetFieldColumnMap()
{
OrmObjectIndex columns = new OrmObjectIndex();
columns.Add(new MapField2Column("SalesOrderDetailID", "SalesOrderDetailID", _tables[1], Field2DbRelations.PK));
columns.Add(new MapField2Column("OrderQty", "OrderQty", _tables[1]));
columns.Add(new MapField2Column("LineTotal", "LineTotal", _tables[1]));
columns.Add(new MapField2Column("OrderDate", "OrderDate", _tables[0]));
columns.Add(new MapField2Column("Territory", "TerritoryID", _tables[0]));
columns.Add(new MapField2Column("Person", "SalesPersonID", _tables[0]));
return columns;
}
public SourceFragment[] GetTables()
{
return _tables;
}
public SourceFragment Table
{
get { return _tables[0]; }
}
}
Now you can use the SalesOrder and don't think about in what table the property you requsted is.
The program prints SalesOrder with a LineTotal less than 10 and OrderDate equals to 2003-08-01.
static void Main(string[] args)
{
foreach (SalesOrder s in SalesOrder.Query
.Where(Ctor
.prop(typeof(SalesOrder), "OrderDate").eq("2003-08-01")
.and(typeof(SalesOrder), "LineTotal").less_than(10))
.ToList())
{
Console.WriteLine("Date: {0}, LineTotal: {1}, sales territory: {2}",
s.OrderDate,
s.LineTotal,
s.Territory.Name);
}
}
Here is generated statement.
declare @p1 VarChar(10);set @p1 = '2003-08-01'
declare @p2 Int;set @p2 = 10
select
--SalesOrder columns
t2.LineTotal, t1.OrderDate, t2.OrderQty, t1.SalesPersonID, t2.SalesOrderDetailID, t1.TerritoryID,
--SalesPerson columns
t3.Bonus, t3.CommissionPct, t3.SalesPersonID, t3.ModifiedDate, t3.SalesLastYear, t3.SalesQuota, t3.TerritoryID, t3.SalesYTD, t3.rowguid,
--Order SalesTerritory columns
t4.CostLastYear, t4.CostYTD, t4.CountryRegionCode, t4.[Group], t4.TerritoryID, t4.ModifiedDate, t4.Name, t4.SalesLastYear, t4.SalesYTD, t4.rowguid,
--Person SalesTerritory columns
t5.CostLastYear, t5.CostYTD, t5.CountryRegionCode, t5.[Group], t5.TerritoryID, t5.ModifiedDate, t5.Name, t5.SalesLastYear, t5.SalesYTD, t5.rowguid
from Sales.SalesOrderHeader t1
join Sales.SalesOrderDetail t2 on t1.SalesOrderID = t2.SalesOrderID
join Sales.SalesPerson t3 on t3.SalesPersonID = t1.SalesPersonID
join Sales.SalesTerritory t4 on t4.TerritoryID = t1.TerritoryID
join Sales.SalesTerritory t5 on t5.TerritoryID = t3.TerritoryID
where (t1.OrderDate = @p1 and t2.LineTotal < @p2)