EF 中如果要用到自定义的参数查询,则如下扩展可以是查询更加方便
public
static
class EntityFrameworkDbExtention
{
public static IEnumerable<T> SqlQueryParameter <T>( this System.Data.Entity.Database database, string sql, object parameters = null)
{
if (parameters == null) return database.SqlQuery<T>(sql);
using ( var tmp_cmd = database.Connection.CreateCommand())
{
var dict = ToDictionary(parameters);
int i = 0;
var arr = new object[dict.Count];
foreach ( var one_kvp in dict)
{
var param = tmp_cmd.CreateParameter();
param.ParameterName = one_kvp.Key;
param.Value = one_kvp.Value ?? DBNull.Value;
arr[i] = param;
i++;
}
return database.SqlQuery<T>(sql, arr);
}
}
private static IDictionary< string, object> ToDictionary( object data)
{
const BindingFlags attr = System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance;
return data.GetType().GetProperties(attr).Where(property => property.CanRead).ToDictionary(property => property.Name, property => property.GetValue(data, null));
}
}
{
public static IEnumerable<T> SqlQueryParameter <T>( this System.Data.Entity.Database database, string sql, object parameters = null)
{
if (parameters == null) return database.SqlQuery<T>(sql);
using ( var tmp_cmd = database.Connection.CreateCommand())
{
var dict = ToDictionary(parameters);
int i = 0;
var arr = new object[dict.Count];
foreach ( var one_kvp in dict)
{
var param = tmp_cmd.CreateParameter();
param.ParameterName = one_kvp.Key;
param.Value = one_kvp.Value ?? DBNull.Value;
arr[i] = param;
i++;
}
return database.SqlQuery<T>(sql, arr);
}
}
private static IDictionary< string, object> ToDictionary( object data)
{
const BindingFlags attr = System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance;
return data.GetType().GetProperties(attr).Where(property => property.CanRead).ToDictionary(property => property.Name, property => property.GetValue(data, null));
}
}
var p = db.Database.SqlQueryParameter<ppc_account>(
"
select * from ppc_account where id=2
").FirstOrDefault();