LINQ to SQL Tips 2: how to use common base class for all entities


Here is another question I get quite often: I have some common fields (maybe id and timestamp) and some common behavior across all my entities. How do I handle that in LINQ to SQL? After all, the only inheritance mapping supported is Table Per Hierarchy (TPH in ORM jargon). Here is a two part answer:


First, you shouldn’t be using mapped inheritance in this case. It is unlikely that you have a single unique, enforced ID-space across all entities (i.e. across all mapped database tables). More likely, you want to encapsulate a pattern and common behavior. The right solution here is an abstract base class that is not mapped. The overridden properties can be mapped in each entity class. Here is a small example based on Northwind database. I have done hand-mapping to keep the example small but you can use SqlMetal to inject a common base class (caution: it is all entities or none, not on a per-entity basis and designer V1 does not support this). You still need to write the abstract base class manually and specify the override property for entity members in the designer. 


 


// Unmapped base class – written manually


abstract class EntityBase


{


    public virtual int ID { get; set; }


}


 


// Mapped derived classes – can be generated using designer/SqlMetal


[Table(Name = “Products”)]


class Product: EntityBase


{


    int ProductID;


    [Column(Name = “ProductID”, Storage = “ProductID”)]


    public override int ID


    {


        get { return ProductID; }


        set { ProductID = value; }


    }


    [Column]


    public string ProductName;


}


 


[Table(Name = “Orders”)]


class Order: EntityBase


{


    int OrderID;


    [Column(Name = “OrderID”, Storage = “OrderID”)]


    public override int ID


    {


        get { return OrderID; }


        set { OrderID = value; }


    }


    [Column]


    public string CustomerID;


}


 


// Strongly typed database connection


class Northwind : DataContext


{


    public Table<Order> Orders;


    public Table<Product> Products;


 


    public Northwind(string s) : base(s) { }


}


 


namespace BaseClassDemo


{   


 


    class Program


    { 


        static void Main(string[] args)


        {


            //NorthwindDataContext db = new NorthwindDataContext();


            Northwind db = new Northwind(@”Server=.\SQLExpress;Database=c:\Northwind\Northwnd.mdf;User Instance=True; Trusted_Connection=True”);


            db.Log = Console.Out;


 


            var query1 = from o in db.Orders


                        where o.CustomerID == “AROUT”


                        select o;


 


            var query2 = from p in db.Products


                        where p.ID < 10


                        select p;


 


            // Write out the results of queries using ObjectDumper – available in VS2008 samples directory


            ObjectDumper.Write(query1);


            ObjectDumper.Write(query2);


 


        }


    }


}


The output is:


WHERE [t0].[CustomerID] = @p0


— @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [AROUT]


— Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.7


 


ID=10355        CustomerID=AROUT


ID=10383        CustomerID=AROUT


ID=10453        CustomerID=AROUT


ID=10558        CustomerID=AROUT


ID=10707        CustomerID=AROUT


ID=10741        CustomerID=AROUT


ID=10743        CustomerID=AROUT


ID=10768        CustomerID=AROUT


ID=10793        CustomerID=AROUT


ID=10864        CustomerID=AROUT


ID=10920        CustomerID=AROUT


ID=10953        CustomerID=AROUT


ID=11016        CustomerID=AROUT


ID=11081        CustomerID=AROUT


SELECT [t0].[ProductName], [t0].[ProductID] AS [ID]


FROM [Products] AS [t0]


WHERE [t0].[ProductID] < @p0


— @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]


— Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.7


 


ID=1    ProductName=Chai


ID=2    ProductName=Chang


ID=3    ProductName=Aniseed Syrup


ID=4    ProductName=Chef Anton’s Cajun Seasoning


ID=5    ProductName=Chef Anton’s Gumbo Mix


ID=6    ProductName=Grandma’s Boysenberry Spread


ID=7    ProductName=Uncle Bob’s Organic Dried Pears


ID=8    ProductName=Northwoods Cranberry Sauce


ID=9    ProductName=Mishi Kobe Niku


Press any key to continue . . .


 


 


Comments (6)

  1. Alexey Lavnikov says:

    What was a problem to gather ColumnAttributes  from flattened list of properties?

    Doesn’t look very pretty to have virtual properties just for mapping purpose…

  2. Martin Laufer says:

    Hi,

    You stated, that the sqlmetal tool will accept only one base class for all entities. Will it accept an interface?

    – Martin

  3. Hot Topics says:

    Dinesh Kularni , who was formerly on the LINQ to SQL team and is now on the Silverlight team, has been

  4. Dinesh Kularni a publié depuis novembre 5 astuces sur LINQ To SQL : LINQ to SQL Tips 1: how to map an

  5. I found a series of LINQ to SQL tips over at Dinesh’s Cyberstation . LINQ to SQL Tips 1: how to map an enum LINQ to SQL Tips 2: how to use common base class for all entities LINQ to SQL Tips 3: Deferred (lazy) or eager loading of related objects with

  6. Üks teemaisd, mis mind mõnda aega on Linq to SQL juures mõtisklema pannud, on selle lifetime ehk kui