AdventureWorks mapping: multitable entity

by Alexey Shirshov April 17, 2009 11:21

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)

Comments

Add comment


(Will show your Gravatar icon)  

Enter the word
captcha word
(hear it spoken)


  Country flag

biuquote
  • Comment
  • Preview
Loading



Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen | Modified by Mooglegiant

The Author

My name is Alexey Shirshov. I'm a professional developer with wide specialization. I prefer VB.NET to C#, I hate ASP.NET but there is no better than it. You can contact me by this page.