C#中的LINQ to SQL:重构这个通用GetByID方法 [英] C# LINQ to SQL: Refactoring this Generic GetByID method
问题描述
我写了下面的方法。
公共牛逼GetByID(INT ID)
{
VAR的DbContext = DB;
无功表= dbcontext.GetTable< T>();
返回table.ToList()的SingleOrDefault(E => Convert.ToInt16(e.GetType()的GetProperties()(第)的GetValue(即空))== ID。);
}
基本上它是在一个泛型类中的方法,其中 T
是一个DataContext类。
的方法,从T(的GetTable
),并检查用于第一属性(总是作为ID),所输入的参数的类型得到表
这个问题是我不得不元素表转换到一个列表的财产执行的GetType
,但这不是因为所有的非常方便表中的元素都被列举并转换为列表
。
我怎样才能重构这种方法避免了了ToList
整个表上?
[更新]
我之所以不能执行直接其中,
在桌子上,是因为我收到此异常:
方法System.Reflection.PropertyInfo []的GetProperties()'没有支持转换为SQL。
块引用>由于
的GetProperties
不能转换为SQL。[更新]
有些人认为使用一个接口的 T 的,但问题是,
T
参数将是在自动生成的类 [DataContextName]了.Designer.cs 的,因此,我不能让它实现一个接口(和它的不可行实施LINQ的所有这些数据库类的接口;而且,该文件将被重新生成一旦我添加新表到DataContext,从而失去所有写入的数据)。所以,必须有更好的方法来做到这一点...
[更新]
我现在已经实现我的code像<一个href=\"http://stackoverflow.com/questions/735140/c-linq-to-sql-refectoring-this-generic-getbyid-method/735209#735209\">Neil威廉姆斯的建议,但我仍然有问题。下面是code的摘录:
的接口:的
公共接口IHasID
{
INT ID {搞定;组; }
}的的DataContext [查看code]:的
命名空间MusicRepo_DataContext
{
部分类艺术家:IHasID
{
公众诠释ID
{
{返回ArtistID; }
集合{抛出新System.NotImplementedException(); }
}
}
}的泛型方法:的
公共类DBACCESS&LT; T&GT;其中T:类,IHasID,新的()
{
公共ŧGetByID(INT ID)
{
VAR的DbContext = DB;
无功表= dbcontext.GetTable&LT; T&GT;(); 返回table.SingleOrDefault(E =&GT; e.ID.Equals(ID));
}
}该异常被抛出这一行:
返回table.SingleOrDefault(E =&GT; e.ID.Equals(ID));
和例外是:
System.NotSupportedException:本
会员
MusicRepo_DataContext.IHasID.ID'有
没有支持转换为SQL。
块引用>[更新]解决方法:
随着<一个帮助href=\"http://stackoverflow.com/questions/735140/c-linq-to-sql-refectoring-this-generic-getbyid-method/735888#735888\">Denis的控制器张贴的答案,并在 code咆哮博客,我终于找到一个解决办法:
公共静态的PropertyInfo的getPrimaryKey(这种类型的EntityType)
{
的foreach(在entityType.GetProperties的PropertyInfo属性())
{
ColumnAttribute [] =属性(ColumnAttribute [])property.GetCustomAttributes(typeof运算(ColumnAttribute),TRUE);
如果(attributes.Length == 1)
{
ColumnAttribute columnAttribute =属性[0];
如果(columnAttribute.IsPrimaryKey)
{
如果(property.PropertyType!= typeof运算(INT))
{
抛出新ApplicationException的(的String.Format(主键,{0},类型为{1}不INT,
property.Name,的EntityType));
}
返回财产;
}
}
}
抛出新ApplicationException的(的String.Format(类型没有定义主键{0},entityType.Name));
}公共ŧGetByID(INT ID)
{
VAR的DbContext = DB; VAR itemParameter =前pression.Parameter(typeof运算(T),项目);
VAR whereEx pression =前pression.Lambda&LT;&Func键LT; T,BOOL&GT;&GT;
(
防爆pression.Equal(
防爆pression.Property(
itemParameter,
typeof运算(T).GetPrimaryKey()。名称
)
防爆pression.Constant(ID)
)
新的[] {} itemParameter
);
返回dbcontext.GetTable&LT; T&GT;()式(whereEx pression)。单()。
}
解决方案您需要的是建立一个前pression树的 LINQ&NBSP;到&NBSP; SQL 可以理解。假设你的ID属性,始终被命名为ID:
公众实际的t GetById&LT; T&GT;(短ID)
{
VAR itemParameter =前pression.Parameter(typeof运算(T),项目);
VAR whereEx pression =前pression.Lambda&LT;&Func键LT; T,BOOL&GT;&GT;
(
防爆pression.Equal(
防爆pression.Property(
itemParameter,
ID
)
防爆pression.Constant(ID)
)
新的[] {} itemParameter
);
无功表= DB.GetTable&LT; T&GT;();
返回table.Where(whereEx pression)。单();
}这应该做的伎俩。它是无耻地从借这个博客。
查询SQL不
这基本上就是LINQ&NBSP;到&NBSP;当你写像变种Q =从吨Context.GetTable&LT; T)()
其中,t.id == ID
选择吨;您只需做LTS的工作,因为编译器不能创建适合你,因为没有什么可以强制T有一个id属性,不能从对数据库的接口映射一个任意的ID属性。
==== ====更新
OK,下面是查找主键名称的简单实现,假设只有一个(不是复合主键),并假设一切都很好型明智的(也就是你的主键与短兼容键入您在GetById功能使用):
公众实际的t GetById&LT; T&GT;(短ID)
{
VAR itemParameter =前pression.Parameter(typeof运算(T),项目);
VAR whereEx pression =前pression.Lambda&LT;&Func键LT; T,BOOL&GT;&GT;
(
防爆pression.Equal(
防爆pression.Property(
itemParameter,
GetPrimaryKeyName&LT; T&GT;()
)
防爆pression.Constant(ID)
)
新的[] {} itemParameter
);
无功表= DB.GetTable&LT; T&GT;();
返回table.Where(whereEx pression)。单();
}
公共字符串GetPrimaryKeyName&LT; T&GT;()
{
VAR类型= Mapping.GetMetaType(typeof运算(T)); VAR PK =(从type.DataMembers米
其中,m.IsPrimaryKey
选择M)。单();
返回PK.Name;
}I wrote the following method.
public T GetByID(int id) { var dbcontext = DB; var table = dbcontext.GetTable<T>(); return table.ToList().SingleOrDefault(e => Convert.ToInt16(e.GetType().GetProperties().First().GetValue(e, null)) == id); }
Basically it's a method in a Generic class where
T
is a class in a DataContext.The method gets the table from the type of T (
GetTable
) and checks for the first property (always being the ID) to the inputted parameter.The problem with this is I had to convert the table of elements to a list first to execute a
GetType
on the property, but this is not very convenient because all the elements of the table have to be enumerated and converted to aList
.How can I refactor this method to avoid a
ToList
on the whole table?[Update]
The reason I can't execute the
Where
directly on the table is because I receive this exception:Method 'System.Reflection.PropertyInfo[] GetProperties()' has no supported translation to SQL.
Because
GetProperties
can't be translated to SQL.[Update]
Some people have suggested using an interface for T, but the problem is that the
T
parameter will be a class that is auto generated in [DataContextName].designer.cs, and thus I cannot make it implement an interface (and it's not feasible implementing the interfaces for all these "database classes" of LINQ; and also, the file will be regenerated once I add new tables to the DataContext, thus loosing all the written data).So, there has to be a better way to do this...
[Update]
I have now implemented my code like Neil Williams' suggestion, but I'm still having problems. Here are excerpts of the code:
Interface:
public interface IHasID { int ID { get; set; } }
DataContext [View Code]:
namespace MusicRepo_DataContext { partial class Artist : IHasID { public int ID { get { return ArtistID; } set { throw new System.NotImplementedException(); } } } }
Generic Method:
public class DBAccess<T> where T : class, IHasID,new() { public T GetByID(int id) { var dbcontext = DB; var table = dbcontext.GetTable<T>(); return table.SingleOrDefault(e => e.ID.Equals(id)); } }
The exception is being thrown on this line:
return table.SingleOrDefault(e => e.ID.Equals(id));
and the exception is:
System.NotSupportedException: The member 'MusicRepo_DataContext.IHasID.ID' has no supported translation to SQL.
[Update] Solution:
With the help of Denis Troller's posted answer and the link to the post at the Code Rant blog, I finally managed to find a solution:
public static PropertyInfo GetPrimaryKey(this Type entityType) { foreach (PropertyInfo property in entityType.GetProperties()) { ColumnAttribute[] attributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), true); if (attributes.Length == 1) { ColumnAttribute columnAttribute = attributes[0]; if (columnAttribute.IsPrimaryKey) { if (property.PropertyType != typeof(int)) { throw new ApplicationException(string.Format("Primary key, '{0}', of type '{1}' is not int", property.Name, entityType)); } return property; } } } throw new ApplicationException(string.Format("No primary key defined for type {0}", entityType.Name)); } public T GetByID(int id) { var dbcontext = DB; var itemParameter = Expression.Parameter(typeof (T), "item"); var whereExpression = Expression.Lambda<Func<T, bool>> ( Expression.Equal( Expression.Property( itemParameter, typeof (T).GetPrimaryKey().Name ), Expression.Constant(id) ), new[] {itemParameter} ); return dbcontext.GetTable<T>().Where(whereExpression).Single(); }
解决方案What you need is to build an expression tree that LINQ to SQL can understand. Assuming your "id" property is always named "id":
public virtual T GetById<T>(short id) { var itemParameter = Expression.Parameter(typeof(T), "item"); var whereExpression = Expression.Lambda<Func<T, bool>> ( Expression.Equal( Expression.Property( itemParameter, "id" ), Expression.Constant(id) ), new[] { itemParameter } ); var table = DB.GetTable<T>(); return table.Where(whereExpression).Single(); }
This should do the trick. It was shamelessly borrowed from this blog. This is basically what LINQ to SQL does when you write a query like
var Q = from t in Context.GetTable<T)() where t.id == id select t;
You just do the work for LTS because the compiler cannot create that for you, since nothing can enforce that T has an "id" property, and you cannot map an arbitrary "id" property from an interface to the database.
==== UPDATE ====
OK, here's a simple implementation for finding the primary key name, assuming there is only one (not a composite primary key), and assuming all is well type-wise (that is, your primary key is compatible with the "short" type you use in the GetById function):
public virtual T GetById<T>(short id) { var itemParameter = Expression.Parameter(typeof(T), "item"); var whereExpression = Expression.Lambda<Func<T, bool>> ( Expression.Equal( Expression.Property( itemParameter, GetPrimaryKeyName<T>() ), Expression.Constant(id) ), new[] { itemParameter } ); var table = DB.GetTable<T>(); return table.Where(whereExpression).Single(); } public string GetPrimaryKeyName<T>() { var type = Mapping.GetMetaType(typeof(T)); var PK = (from m in type.DataMembers where m.IsPrimaryKey select m).Single(); return PK.Name; }
这篇关于C#中的LINQ to SQL:重构这个通用GetByID方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!