C# LINQ to SQL：重构这个通用的 GetByID 方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/735140/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# LINQ to SQL: Refactoring this Generic GetByID method
提问by Andreas Grech
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 Tis a class in a DataContext.
基本上它是泛型类中的一个方法，其中T是 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.
该方法从 T ( GetTable)的类型中获取表，并检查输入参数的第一个属性（始终是 ID）。
The problem with this is I had to convert the table of elements to a list first to execute a GetTypeon the property, but this is not very convenient because all the elements of the table have to be enumerated and converted to a List.
这样做的问题是我必须首先将元素表转换为列表才能GetType对属性执行 a ，但这不是很方便，因为必须枚举表中的所有元素并将其转换为 a List。
How can I refactor this method to avoid a ToListon the whole table?
如何重构此方法以避免ToList在整个表上出现 a？
[Update]
[更新]
The reason I can't execute the Wheredirectly on the table is because I receive this exception:
我不能Where直接在表上执行的原因是因为我收到了这个异常：
Method 'System.Reflection.PropertyInfo[] GetProperties()' has no supported translation to SQL.
方法“System.Reflection.PropertyInfo[] GetProperties()”不支持转换为 SQL。
Because GetPropertiescan't be translated to SQL.
因为GetProperties不能翻译成SQL。
[Update]
[更新]
Some people have suggested using an interface for T, but the problem is that the Tparameter 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).
有些人建议为T使用接口，但问题是该T参数将是一个在[DataContextName].designer.cs 中自动生成的类，因此我无法使其实现接口（并且实现该接口是不可行的） LINQ 的所有这些“数据库类”的接口；而且，一旦我将新表添加到 DataContext，该文件将重新生成，从而丢失所有写入的数据）。
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]:
DataContext [查看代码]：
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:
在这一行抛出异常：return table.SingleOrDefault(e => e.ID.Equals(id));并且异常是：
System.NotSupportedException: The member 'MusicRepo_DataContext.IHasID.ID' has no supported translation to SQL.
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:
在Denis Troller发布的答案和Code Rant 博客上的帖子链接的帮助下，我终于找到了解决方案：
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();
}
采纳答案by Denis Troller
What you need is to build an expression tree that LINQ to SQLcan understand. Assuming your "id" property is always named "id":
您需要的是构建一个LINQ to SQL可以理解的表达式树。假设您的“id”属性始终命名为“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
这应该可以解决问题。这是无耻地从这个博客借来的。这基本上就是 LINQ to SQL 在编写查询时所做的事情
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.
您只是为 LTS 完成工作，因为编译器无法为您创建它，因为没有什么可以强制 T 具有“id”属性，并且您不能将任意“id”属性从接口映射到数据库。
==== 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):
好的，这里有一个查找主键名称的简单实现，假设只有一个（不是复合主键），并假设所有类型都很好（也就是说，您的主键与您的“短”类型兼容）在 GetById 函数中使用）：
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;
}
回答by Reed Copsey
What if you rework this to use GetTable().Where(...), and put your filtering there?
如果你修改它以使用 GetTable().Where(...)，并将你的过滤放在那里怎么办？
That would be more efficient, since the Where extension method should take care of your filtering better than fetching the entire table into a list.
这会更有效，因为 Where 扩展方法应该比将整个表提取到列表中更好地处理您的过滤。
回答by sisve
Some thoughts...
一些想法...
Just remove the ToList() call, SingleOrDefault works with an IEnumerably which I presume table is.
只需删除 ToList() 调用，SingleOrDefault 就可以使用我认为表是的 IEnumerably。
Cache the call to e.GetType().GetProperties().First() to get the PropertyInfo returned.
缓存对 e.GetType().GetProperties().First() 的调用以获取返回的 PropertyInfo。
Cant you just add a constraint to T that would force them to implement an interface that exposes the Id property?
难道你不能只向 T 添加一个约束来强制他们实现一个公开 Id 属性的接口吗？
回答by Misha N.
Maybe executing a query might be a good idea.
也许执行查询可能是个好主意。
public static T GetByID(int id)
{
Type type = typeof(T);
//get table name
var att = type.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
string tablename = att == null ? "" : ((TableAttribute)att).Name;
//make a query
if (string.IsNullOrEmpty(tablename))
return null;
else
{
string query = string.Format("Select * from {0} where {1} = {2}", new object[] { tablename, "ID", id });
//and execute
return dbcontext.ExecuteQuery<T>(query).FirstOrDefault();
}
}
回答by Denis Troller
Regarding:
关于：
System.NotSupportedException: The member 'MusicRepo_DataContext.IHasID.ID' has no supported translation to SQL.
System.NotSupportedException：成员“MusicRepo_DataContext.IHasID.ID”不支持转换为 SQL。
The simple workaround to your initial problem is to specify an Expression. See below, it works like a charm for me.
解决初始问题的简单方法是指定一个表达式。见下文，它对我来说就像一个魅力。
public interface IHasID
{
int ID { get; set; }
}
DataContext [View Code]:
namespace MusicRepo_DataContext
{
partial class Artist : IHasID
{
[Column(Name = "ArtistID", Expression = "ArtistID")]
public int ID
{
get { return ArtistID; }
set { throw new System.NotImplementedException(); }
}
}
}
回答by Gildor
Ok, check this demo implementation. Is attempt to get generic GetById with datacontext(Linq To Sql). Also compatible with multi key property.
好的，检查这个演示实现。尝试使用 datacontext(Linq To Sql) 获取通用 GetById。也兼容多键属性。
using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Reflection;
using System.Collections.Generic;
public static class Programm
{
public const string ConnectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=TestDb2;Persist Security Info=True;integrated Security=True";
static void Main()
{
using (var dc = new DataContextDom(ConnectionString))
{
if (dc.DatabaseExists())
dc.DeleteDatabase();
dc.CreateDatabase();
dc.GetTable<DataHelperDb1>().InsertOnSubmit(new DataHelperDb1() { Name = "DataHelperDb1Desc1", Id = 1 });
dc.GetTable<DataHelperDb2>().InsertOnSubmit(new DataHelperDb2() { Name = "DataHelperDb2Desc1", Key1 = "A", Key2 = "1" });
dc.SubmitChanges();
Console.WriteLine("Name:" + GetByID(dc.GetTable<DataHelperDb1>(), 1).Name);
Console.WriteLine("");
Console.WriteLine("");
Console.WriteLine("Name:" + GetByID(dc.GetTable<DataHelperDb2>(), new PkClass { Key1 = "A", Key2 = "1" }).Name);
}
}
//Datacontext definition
[Database(Name = "TestDb2")]
public class DataContextDom : DataContext
{
public DataContextDom(string connStr) : base(connStr) { }
public Table<DataHelperDb1> DataHelperDb1;
public Table<DataHelperDb2> DataHelperD2;
}
[Table(Name = "DataHelperDb1")]
public class DataHelperDb1 : Entity<DataHelperDb1, int>
{
[Column(IsPrimaryKey = true)]
public int Id { get; set; }
[Column]
public string Name { get; set; }
}
public class PkClass
{
public string Key1 { get; set; }
public string Key2 { get; set; }
}
[Table(Name = "DataHelperDb2")]
public class DataHelperDb2 : Entity<DataHelperDb2, PkClass>
{
[Column(IsPrimaryKey = true)]
public string Key1 { get; set; }
[Column(IsPrimaryKey = true)]
public string Key2 { get; set; }
[Column]
public string Name { get; set; }
}
public class Entity<TEntity, TKey> where TEntity : new()
{
public static TEntity SearchObjInstance(TKey key)
{
var res = new TEntity();
var targhetPropertyInfos = GetPrimaryKey<TEntity>().ToList();
if (targhetPropertyInfos.Count == 1)
{
targhetPropertyInfos.First().SetValue(res, key, null);
}
else if (targhetPropertyInfos.Count > 1)
{
var sourcePropertyInfos = key.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (var sourcePi in sourcePropertyInfos)
{
var destinationPi = targhetPropertyInfos.FirstOrDefault(x => x.Name == sourcePi.Name);
if (destinationPi == null || sourcePi.PropertyType != destinationPi.PropertyType)
continue;
object value = sourcePi.GetValue(key, null);
destinationPi.SetValue(res, value, null);
}
}
return res;
}
}
public static IEnumerable<PropertyInfo> GetPrimaryKey<T>()
{
foreach (var info in typeof(T).GetProperties().ToList())
{
if (info.GetCustomAttributes(false)
.Where(x => x.GetType() == typeof(ColumnAttribute))
.Where(x => ((ColumnAttribute)x).IsPrimaryKey)
.Any())
yield return info;
}
}
//Move in repository pattern
public static TEntity GetByID<TEntity, TKey>(Table<TEntity> source, TKey id) where TEntity : Entity<TEntity, TKey>, new()
{
var searchObj = Entity<TEntity, TKey>.SearchObjInstance(id);
Console.WriteLine(source.Where(e => e.Equals(searchObj)).ToString());
return source.Single(e => e.Equals(searchObj));
}
}
Result:
结果：
SELECT [t0].[Id], [t0].[Name]
FROM [DataHelperDb1] AS [t0]
WHERE [t0].[Id] = @p0
Name:DataHelperDb1Desc1
SELECT [t0].[Key1], [t0].[Key2], [t0].[Name]
FROM [DataHelperDb2] AS [t0]
WHERE ([t0].[Key1] = @p0) AND ([t0].[Key2] = @p1)
Name:DataHelperDb2Desc1
