问题描述
我写了下面的方法.
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);
}
基本上它是泛型类中的一个方法,其中 T
是 DataContext 中的一个类.
Basically it's a method in a Generic class where T
is a class in a DataContext.
该方法从 T 的类型 (GetTable
) 中获取表,并检查输入参数的第一个属性(始终是 ID).
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.
这样做的问题是我必须先将元素表转换为列表才能在属性上执行 GetType
,但这不是很方便,因为表中的所有元素都必须被枚举并转换为List
.
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 a List
.
如何重构此方法以避免在整个表上出现 ToList
?
How can I refactor this method to avoid a ToList
on the whole table?
[更新]
我不能直接在表上执行Where
的原因是因为我收到这个异常:
The reason I can't execute the Where
directly on the table is because I receive this exception:
方法System.Reflection.PropertyInfo[] GetProperties()"不支持转换为 SQL.
因为 GetProperties
无法转换为 SQL.
Because GetProperties
can't be translated to SQL.
[更新]
有些人建议为 T 使用接口,但问题是 T
参数将是一个在 [DataContextName] 中自动生成的类.designer.cs,因此我不能让它实现一个接口(并且为 LINQ 的所有这些数据库类"实现接口是不可行的;而且,一旦我添加新表,文件将重新生成DataContext,从而丢失所有写入的数据).
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...
[更新]
我现在已经实现了我的代码,如 尼尔威廉姆斯的建议,但我仍然遇到问题.以下是代码摘录:
I have now implemented my code like Neil Williams' suggestion, but I'm still having problems. Here are excerpts of the code:
界面:
public interface IHasID
{
int ID { get; set; }
}
DataContext [查看代码]:
namespace MusicRepo_DataContext
{
partial class Artist : IHasID
{
public int ID
{
get { return ArtistID; }
set { throw new System.NotImplementedException(); }
}
}
}
通用方法:
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));
}
}
在这一行抛出异常: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:成员'MusicRepo_DataContext.IHasID.ID' 有不支持转换为 SQL.
[更新] 解决方案:
在 Denis Troller 的帮助下 在 Code Rant 博客,我终于找到了解决方案:
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();
}
推荐答案
您需要构建一个表达式树,LINQ to SQL 可以理解.假设您的id"属性始终命名为id":
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();
}
这应该可以解决问题.无耻地从这个博客借来的.这基本上就是 LINQ to SQL 在编写像
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;
您只是为 LTS 做这项工作,因为编译器无法为您创建它,因为没有什么可以强制 T 具有id"属性,并且您不能将任意id"属性从接口映射到数据库.
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 ====
好的,这里有一个查找主键名称的简单实现,假设只有一个(不是复合主键),并假设所有类型都很好(也就是说,您的主键与short" 您在 GetById 函数中使用的类型):
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 方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!