In my last post I rendered some opinions on how to approach using LINQ to SQL in an encapsulated manner. In response, several folks requested I put together some more concrete examples. I have now done so. Pardon the high code-to-text ratio: I'm not feeling particularly poetic at the moment. I also just wrote this code up, so it hasn't had anything more than trivial testing. But, as a place to get people started it's as good as any at the moment.
TableView
The TableView class encapsulates an IQueryable constructed by applying a predicate filter to a Table. This enables the pattern of client-side row restriction by way of forcing WHERE constraints in the generated SQL. For example, permitting the retrieval of only those customers residing in London:
var tableView = new TableView<Customer>(dataContext, c => c.City == "London");
It includes a subset of the functionality of Table, including Attach, Insert and Delete, which it forwards to the encapsulated table provided the entities in question pass the filter.
TableView.cs
using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Linq.Expressions;
namespace Thuban.Data.Linq
{
public sealed class TableView<TEntity> : IQueryable<TEntity>, ITable
where TEntity : class
{
IQueryable<TEntity> baseQuery;
Table<TEntity> table;
Func<TEntity, bool> predicate;
public TableView(DataContext dataContext, Expression<Func<TEntity, bool>> predicate)
{
this.table = dataContext.GetTable<TEntity>();
this.baseQuery = table.Where(predicate);
this.predicate = predicate.Compile();
}
#region IEnumerable<TEntity> Members
public IEnumerator<TEntity> GetEnumerator()
{
return this.baseQuery.GetEnumerator();
}
#endregion
#region IEnumerable Members
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
return this.GetEnumerator();
}
#endregion
#region PermissionChecks
internal void PermissionCheck(Action<TEntity> action, TEntity entity)
{
if (predicate(entity))
{
action(entity);
}
else
{
throw new InvalidOperationException("No permission");
}
}
internal void PermissionCheck(Action<TEntity, TEntity> action, TEntity entity1, TEntity entity2)
{
if (predicate(entity1) && predicate(entity2))
{
action(entity1, entity2);
}
else
{
throw new InvalidOperationException("No permission");
}
}
internal TResult PermissionCheck<TResult>(Func<TEntity, TResult> function, TEntity entity)
{
if (predicate(entity))
{
return function(entity);
}
else
{
throw new InvalidOperationException("No permission");
}
}
internal void PermissionCheck(Action<IEnumerable<TEntity>> action, IEnumerable<TEntity> entities)
{
if (entities.All(predicate))
{
action(entities);
}
else
{
throw new InvalidOperationException("No permission");
}
}
#endregion
#region FilteredTable<TEntity> Members
void Attach(TEntity entity, TEntity original)
{
PermissionCheck((x, y) => table.Attach(x, y), entity, original);
}
void Attach(TEntity entity, bool asModified)
{
PermissionCheck(x => table.Attach(x, asModified), entity);
}
void Attach(TEntity entity)
{
PermissionCheck(x => table.Attach(x), entity);
}
void AttachAll(IEnumerable<TEntity> entities, bool asModified)
{
PermissionCheck(x => table.AttachAll(x, asModified), entities);
}
void AttachAll(IEnumerable<TEntity> entities)
{
PermissionCheck(x => table.AttachAll(x), entities);
}
void DeleteAllOnSubmit(IEnumerable<TEntity> entities)
{
PermissionCheck(x => table.DeleteAllOnSubmit(x), entities);
}
void DeleteOnSubmit(TEntity entity)
{
PermissionCheck(x => table.DeleteOnSubmit(x), entity);
}
ModifiedMemberInfo[] GetModifiedMembers(TEntity entity)
{
return PermissionCheck(x => table.GetModifiedMembers(x), entity);
}
TEntity GetOriginalEntityState(TEntity entity)
{
return PermissionCheck(x => table.GetOriginalEntityState(x), entity);
}
void InsertAllOnSubmit(IEnumerable<TEntity> entities)
{
PermissionCheck(x => table.InsertAllOnSubmit(x), entities);
}
void InsertOnSubmit(TEntity entity)
{
PermissionCheck(x => table.InsertOnSubmit(x), entity);
}
#endregion
#region ITable Members
void ITable.Attach(object entity, object original)
{
this.Attach((TEntity)entity, (TEntity)original);
}
void ITable.Attach(object entity, bool asModified)
{
this.Attach((TEntity)entity, asModified);
}
void ITable.Attach(object entity)
{
this.Attach((TEntity)entity);
}
void ITable.AttachAll(System.Collections.IEnumerable entities, bool asModified)
{
this.AttachAll(entities.Cast<TEntity>(), asModified);
}
void ITable.AttachAll(System.Collections.IEnumerable entities)
{
this.AttachAll(entities.Cast<TEntity>());
}
DataContext ITable.Context
{
get { throw new InvalidOperationException("Access to the underlying context is not allowed."); }
}
void ITable.DeleteAllOnSubmit(System.Collections.IEnumerable entities)
{
this.DeleteAllOnSubmit(entities.Cast<TEntity>());
}
void ITable.DeleteOnSubmit(object entity)
{
this.DeleteOnSubmit((TEntity)entity);
}
ModifiedMemberInfo[] ITable.GetModifiedMembers(object entity)
{
return this.GetModifiedMembers((TEntity)entity);
}
object ITable.GetOriginalEntityState(object entity)
{
return this.GetOriginalEntityState((TEntity)entity);
}
void ITable.InsertAllOnSubmit(System.Collections.IEnumerable entities)
{
this.InsertAllOnSubmit(entities.Cast<TEntity>());
}
void ITable.InsertOnSubmit(object entity)
{
this.InsertOnSubmit((TEntity)entity);
}
bool ITable.IsReadOnly
{
get { return table.IsReadOnly; }
}
#endregion
#region IQueryable Members
Type IQueryable.ElementType
{
get { return baseQuery.ElementType; }
}
Expression IQueryable.Expression
{
get { return baseQuery.Expression; }
}
IQueryProvider IQueryable.Provider
{
get { return baseQuery.Provider; }
}
#endregion
}
}
DataContextWrapper
The other type I've worked on is the abstract class, DataContextWrapper. As TableView does for Table, it encapsulates and provides a subset of the original's function. In particular, it includes a hook for table initialization (not entirely hashed out, to be sure -- I haven't added support for UDFs). I've surfaced some properties of the underlying data context where I thought (quickly) that it made sense to do so, such as CommandTimeout, SubmitChanges, and strongly-typed versions of Refresh; I omitted Log since it's primarily for debugging, and not necessarily appropriate for end-user consumption in this situation. I've also implemented the dispose pattern, and provided a helper method to create a new TableView, for use in the GetQuery(type) initialization hook. Of the two -- TableView and DataContextWrapper -- this is the more primitive.
DataContextWrapper.cs
using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
namespace Thuban.Data.Linq
{
public abstract class DataContextWrapper : IDisposable
{
protected DataContext DataContext { get; private set; }
protected bool IsDisposed { get; private set; }
Dictionary<Type, object> tables = new Dictionary<Type, object>();
protected DataContextWrapper(DataContext dataContext)
{
this.DataContext = dataContext;
this.InitializeTables();
}
void InitializeTables()
{
CheckDisposed();
var filteredTableFields =
this.GetType()
.GetFields(BindingFlags.Public | BindingFlags.Instance)
.Where(fi => fi.FieldType.GetGenericTypeDefinition() == typeof(TableView<>));
foreach (var filteredTableField in filteredTableFields)
{
var type = filteredTableField.FieldType.GetGenericArguments()[0];
var getTable = this.GetType().GetMethod("GetTable").MakeGenericMethod(type);
var table = getTable.Invoke(this, null);
filteredTableField.SetValue(this, table);
}
}
#region Abstract Methods
protected abstract IQueryable GetQuery(Type entityType);
#endregion
#region Helpers
protected TableView<TEntity> CreateTableView<TEntity>(Expression<Func<TEntity, bool>> predicate)
where TEntity : class
{
return new TableView<TEntity>(this.DataContext, predicate);
}
#endregion
#region DataContext Analogues
public int CommandTimeout
{
get
{
return this.DataContext.CommandTimeout;
}
set
{
this.DataContext.CommandTimeout = value;
}
}
public void SubmitChanges()
{
this.DataContext.SubmitChanges();
}
public void SubmitChanges(ConflictMode conflictMode)
{
this.DataContext.SubmitChanges(conflictMode);
}
public TableView<TEntity> GetTable<TEntity>()
where TEntity : class
{
CheckDisposed();
if (tables.Keys.Contains(typeof(TEntity)))
{
return (TableView<TEntity>)tables[typeof(TEntity)];
}
var query = this.GetQuery(typeof(TEntity));
if (query == null)
{
throw new InvalidOperationException("Table of type '" + typeof(TEntity).Name + "' does not have a defined query.");
}
else
{
tables.Add(typeof(TEntity), (TableView<TEntity>)query);
return (TableView<TEntity>)tables[typeof(TEntity)];
}
}
public void Refresh<TEntity>(RefreshMode refreshMode, TEntity entity)
where TEntity : class
{
this.GetTable<TEntity>().PermissionCheck(x => this.DataContext.Refresh(refreshMode, x), entity);
}
public void Refresh<TEntity>(RefreshMode refreshMode, IEnumerable<TEntity> entities)
where TEntity : class
{
this.GetTable<TEntity>().PermissionCheck(x => this.Refresh(refreshMode, x), entities);
}
public void Refresh<TEntity>(RefreshMode refreshMode, params TEntity[] entities)
where TEntity : class
{
this.Refresh(refreshMode, (IEnumerable<TEntity>)entities);
}
#endregion
#region IDisposable Members
protected void CheckDisposed()
{
if (this.IsDisposed)
{
throw new ObjectDisposedException("DataContextWrapper");
}
}
protected virtual void Dispose(bool disposing)
{
if (!this.IsDisposed && disposing)
{
if (this.DataContext != null)
{
this.DataContext.Dispose();
this.IsDisposed = true;
}
}
}
public void Dispose()
{
this.Dispose(true);
// Suppress finalization of this disposed instance.
GC.SuppressFinalize(this);
}
~DataContextWrapper()
{
Dispose(false);
}
#endregion
}
}
Example
So how are these intended to be used? Here's an example DataContextWrapper implementation:
using System;
using System.IO;
using System.Linq;
using Northwind;
using Thuban.Data.Linq;
namespace Sample
{
sealed class NorthwindWrapper: DataContextWrapper
{
public TableView<Customer> Customers;
public NorthwindWrapper()
: base(new NorthwindDataContext())
{
}
public TextWriter Log
{
get
{
return this.DataContext.Log;
}
set
{
this.DataContext.Log = value;
}
}
protected override IQueryable GetQuery(Type entityType)
{
if (entityType == typeof(Customer))
{
return this.CreateTableView<Customer>(c => c.City == "London");
}
else
{
return null;
}
}
}
}
Like the familiar DataContext, you subclass it and add public fields to represent the tables, which are populated during initialization. The DataContextWrapper has only one constructor, which takes the DataContext instance to wrap, and so we forward our default constructor to that. We add a Log property, since this is just a sample, but the most important item is the (required) implementation of GetQuery.
GetQuery accepts the entity type that is being requested, and returns the IQueryable (ie, the TableView) that is appropriate. The interaction here's a bit rough, so I may change it at some point, but it does that it's supposed to for now. Maybe I'll just switch it to use the dictionary directly. Remember -- this is just a quick sketch of one approach.
As you can see in the following, you use it as you would use DataContext normally:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Sample
{
class Program
{
static void Main(string[] args)
{
using (var db = new NorthwindWrapper() { Log = Console.Out })
{
var q = from c in db.Customers
where c.ContactName.StartsWith("B")
select c;
foreach (var item in q)
{
Console.WriteLine("Name = {0}; City = {1}", item.ContactName, item.City);
}
}
Console.ReadKey(true);
}
}
}
And it will produce the following output:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country],
[t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[ContactName] LIKE @p0) AND ([t0].[City] = @p1)
-- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [B%]
-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.7
As you can see, the restriction on City was added automatically.
Future Considerations
This is not the approach that would solve the must-not-be-able-to-access-information-even-through-reflection approach. Reflection is powerful -- if you wanted to, you could access the internals of the change tracker. For those cases -- where information is secured by its absence, rather than secured by hiding it in the closet -- I'd suggest enforcing it at the server. Otherwise, of course, what is to prevent someone from using ADO.NET?
Also, don't fall into the trap of thinking there's only one possible data context for a given database. Create multiple ones when it makes sense: an administrative context with everything, a narrowly-focused context for plug-in modules, and so forth. The context isn't so much the database made manifest as it is just the limit of LINQ to SQL's view. Anything outside that view just isn't known about -- it can't be queried, and it can't be meddled with. Provided you encapsulate your data context, you can prevent gratuitous GetTable calls on newly-mapped types.