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

• Anonymous
  November 18, 2007
  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...

• Anonymous
  November 19, 2007
  Hi, You stated, that the sqlmetal tool will accept only one base class for all entities. Will it accept an interface?

• Martin

• Anonymous
  May 19, 2008
  Dinesh Kularni , who was formerly on the LINQ to SQL team and is now on the Silverlight team, has been

• Anonymous
  May 19, 2008
  Dinesh Kularni a publié depuis novembre 5 astuces sur LINQ To SQL : LINQ to SQL Tips 1: how to map an

• Anonymous
  July 07, 2008
  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

• Anonymous
  March 16, 2009
  Üks teemaisd, mis mind mõnda aega on Linq to SQL juures mõtisklema pannud, on selle lifetime ehk kui