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 . . .