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; }



    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; }



    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







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:


    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

Comments are closed.

Skip to main content