C# LINQ to SQL:重构这个通用的 GetByID 方法 [英] C# LINQ to SQL: Refactoring this Generic GetByID 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);
}
基本上它是泛型类中的一个方法，其中 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.
Method 'System.Reflection.PropertyInfo[] GetProperties()' has no supported translation to 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 方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
