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

Skip to main content