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 beenAnonymous
May 19, 2008
Dinesh Kularni a publié depuis novembre 5 astuces sur LINQ To SQL : LINQ to SQL Tips 1: how to map anAnonymous
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 withAnonymous
March 16, 2009
Üks teemaisd, mis mind mõnda aega on Linq to SQL juures mõtisklema pannud, on selle lifetime ehk kui