使用bubi的方法几年后，我实现了一些代码，并决定在这里发布我们的改进。请注意，有对其他命名空间的引用，我不会发布。只需根据您的需求进行调整。
无论如何，我希望它能帮助到某些人。
using System;
using System.Collections.Generic;
using System.Collections.Immutable;
using System.Data;
using System.Data.Common;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Mapping;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
public abstract partial class BaseService
where TEntity : EntityDefault
{
private const int MAX_ITEMS_PER_PREDICATE = 500;
///
/// Lista imutável contendo todos os predicates, por tipo da entidade, a serem buscados no banco de dados.
///
private ImmutableDictionary> Predicates { get; set; }
private ImmutableDictionary PredicatesCount { get; set; }
private ImmutableDictionary> LoadedPredicates { get; set; }
///
/// Lista imutável contendo as entidades, que são propriedades de navegação, já buscadas no banco de dados.
///
private ImmutableList NavigationEntities { get; set; }
///
/// Lista imutável contendo todas as propriedades de navegação
///
private ImmutableList NavigationProperties { get; set; }
///
/// Maps the result of a query into entities.
///
///
/// The SQL query.
/// List of parameters to be passed to the procedure
///
/// It might return null when query is null or empty.
/// An entity list
///
/// context
/// or
/// queryConnection
/// or
/// sqlQuery
///
public List SqlQuery(string query, Dictionary parameters, params KeyValuePair[] options) where T : EntityDefault
{
DbConnection queryConnection = null;
try
{
InitOrResetSqlQueryVariables();
if (query.HasntValue())
{
throw new ArgumentNullException(nameof(query));
}
queryConnection = Db.Database.Connection;
var connectionState = queryConnection.State;
if (connectionState != ConnectionState.Open)
{
queryConnection.Open();
}
var command = queryConnection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
if (parameters != null)
{
command.AddParameters(parameters);
}
var reader = command.ExecuteReader();
var entities = new List();
while (reader.Read())
{
entities.Add(MapEntity(reader));
}
LoadNavigationProperties(entities, options);
return entities;
}
finally
{
InitOrResetSqlQueryVariables();
if (Db.BaseDb.AutoCloseConnection && queryConnection != null)
{
if (queryConnection.State != ConnectionState.Closed)
{
queryConnection.Close();
}
queryConnection.Dispose();
}
}
}
public List SqlQuery(string query, List parameters, params KeyValuePair[] options) where T : EntityDefault
{
DbConnection queryConnection = null;
try
{
InitOrResetSqlQueryVariables();
if (query.HasntValue())
{
throw new ArgumentNullException(nameof(query));
}
queryConnection = Db.Database.Connection;
var connectionState = queryConnection.State;
if (connectionState != ConnectionState.Open)
{
queryConnection.Open();
}
var command = queryConnection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query;
if (parameters != null)
{
command.Parameters.AddRange(parameters.ToArray());
}
var reader = command.ExecuteReader();
var entities = new List();
while (reader.Read())
{
entities.Add(MapEntity(reader));
}
LoadNavigationProperties(entities, options);
return entities;
}
finally
{
InitOrResetSqlQueryVariables();
if (Db.BaseDb.AutoCloseConnection && queryConnection != null)
{
if (queryConnection.State != ConnectionState.Closed)
{
queryConnection.Close();
}
queryConnection.Dispose();
}
}
}
private T MapEntity(IDataRecord reader)
{
var entityObject = Activator.CreateInstance();
MapEntity(reader, entityObject);
return entityObject;
}
private void MapEntity(IDataRecord reader, object entityObject)
{
var objectContext = ((IObjectContextAdapter)Db).ObjectContext;
var metadataWorkspace = ((EntityConnection)objectContext.Connection).GetMetadataWorkspace();
var entitySetMappingCollection =
metadataWorkspace.GetItems(DataSpace.CSSpace).Single().EntitySetMappings;
var associationSetMappingCollection =
metadataWorkspace.GetItems(DataSpace.CSSpace)
.Single()
.AssociationSetMappings.ToList();
var entitySetMappings =
entitySetMappingCollection.First(
o => o.EntityTypeMappings.Select(e => e.EntityType.Name).Contains(entityObject.GetType().Name));
var entityTypeMapping = entitySetMappings.EntityTypeMappings[0];
var tableName = entityTypeMapping.EntitySetMapping.EntitySet.Name;
Debug.WriteLine(tableName);
var mappingFragment = entityTypeMapping.Fragments[0];
// Maps the properties of the entity itself
foreach (var propertyMapping in mappingFragment.PropertyMappings)
{
var valueBeforCasting = reader[((ScalarPropertyMapping)propertyMapping).Column.Name];
var value = valueBeforCasting is DBNull
? null
: propertyMapping.Property.IsEnumType
? Convert.ChangeType(valueBeforCasting,
typeof(int))
: Convert.ChangeType(valueBeforCasting,
propertyMapping.Property.PrimitiveType.ClrEquivalentType);
entityObject.GetType()
.GetProperty(propertyMapping.Property.Name)
.SetValue(entityObject, value, null);
Debug.WriteLine("{0} {1} {2}", propertyMapping.Property.Name,
((ScalarPropertyMapping)propertyMapping).Column, value);
}
if (NavigationProperties.Count == 0)
{
NavigationProperties = NavigationProperties.AddRange(entityTypeMapping.EntityType.NavigationProperties);
}
// Maps the associated navigational properties
foreach (var navigationProperty in NavigationProperties)
{
var propertyInfo = entityObject.GetType().GetProperty(navigationProperty.Name);
// TODO: Por Marco em 26/11/2015
/*
* Verificar em QueryOptions (que neste momento não é passada para esta rotina) se foi solicitado Eager Loading desta navigationProperty.
* Caso negativo executar um "continue;"
*
* Isso ajudará a evitar consultas desnecessárias ao banco de dados.
*/
var propertyType = propertyInfo.PropertyType;
var associationSetMapping =
associationSetMappingCollection.First(
a => a.AssociationSet.ElementType.FullName == navigationProperty.RelationshipType.FullName);
// associationSetMapping.AssociationTypeMapping.MappingFragment.PropertyMappings contains two elements one for direct and one for inverse relationship
var propertyMappings =
associationSetMapping.AssociationTypeMapping.MappingFragment.PropertyMappings
.Cast().First(p => p.AssociationEnd.Name.EndsWith("_Target"));
var key = propertyMappings.PropertyMappings.Select(c => reader[c.Column.Name]).ToArray();
if (!key.Any() || key[0] is DBNull)
continue;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Monta o PredicateBuilder que será utilizado para trazer todas as entidades associadas solicitadas
var outerPredicate = typeof(PredicateBuilder).InvokeStaticGenericMethod(propertyType, "False");
if (!Predicates.ContainsKey(propertyType))
{
var predicatesList = new List { outerPredicate };
Predicates = Predicates.Add(propertyType, predicatesList);
LoadedPredicates = LoadedPredicates.Add(propertyType, new List());
PredicatesCount = PredicatesCount.Add(propertyType, 0);
}
var loadedPredicates = LoadedPredicates[propertyType];
if (loadedPredicates.All(p => p != Convert.ToInt32(key[0])))
{
loadedPredicates.Add(Convert.ToInt32(key[0]));
BuildPredicate(propertyType, outerPredicate, Convert.ToInt32(key[0]));
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Seta o Id como helper para a rotina LoadAssociatedEntities
var value = Activator.CreateInstance(propertyType);
var idProperty = propertyType.GetProperty("Id");
idProperty.SetValue(value, key[0]);
propertyInfo.SetValue(entityObject, value, null);
}
}
private void BuildPredicate(Type propertyType, object outerPredicate, int pkValue)
{
var parameter = Expression.Parameter(propertyType, "p");
var property = Expression.Property(parameter, "Id");
var valueToCompare = Expression.Constant(pkValue);
var equalsExpression = Expression.Equal(property, valueToCompare);
var funcType = typeof(Func).MakeGenericType(propertyType, typeof(bool));
var lambdaExpression = Expression.Lambda(funcType, equalsExpression, parameter);
var predicateList = Predicates[propertyType];
var predicatesCount = PredicatesCount[propertyType];
if (predicatesCount % MAX_ITEMS_PER_PREDICATE == 0)
{
predicateList.Add(outerPredicate);
}
var predicate = predicateList.Last();
predicate = typeof(PredicateBuilder).InvokeStaticGenericMethod(propertyType, "Or", predicate, lambdaExpression);
predicateList[predicateList.Count - 1] = predicate;
predicatesCount++;
PredicatesCount = PredicatesCount.Replace(propertyType, predicatesCount);
}
///
/// Carrega as entidades associadas solicitadas via EagerLoading
///
/// Tipo específico de EntityDefault
/// Lista de entidades que irão ter as entidades associadas carregadas
/// Array de Eager Loadings a serem carregados
private void LoadNavigationProperties(IReadOnlyList entities,
params KeyValuePair[] eagerLoadings) where T : EntityDefault
{
foreach (var predicateItem in Predicates)
{
var newEagerLoadings = new List>();
var newOptions =
eagerLoadings
.Where(p => p.Key == QueryOptions.DefineInclude || p.Key == QueryOptions.DefineIncludes)
.ToList();
var predicateWhere = predicateItem;
// Loop em todas as propriedades de navegação de T que sejam do mesmo tipo do predicate.Key
// Esse loop terá alimentado newEagerLoadings com os valores adequados.
foreach (
var navigationProperty in
NavigationProperties.Where(
p => entities[0].GetType().GetProperty(p.Name).PropertyType == predicateWhere.Key))
{
newOptions =
newOptions.Where(p => p.Value.ToString().StartsWith(navigationProperty.Name)).ToList();
if (!newOptions.Any())
continue;
// ReSharper disable once LoopCanBeConvertedToQuery
foreach (var option in newOptions)
{
if (!option.Value.ToString().Contains("."))
{
continue;
}
var newOption = Pairing.Of(option.Key,
option.Value.ToString()
.RemovePrefix(navigationProperty.Name + ".")
.RemovePrefix(navigationProperty.Name));
if (newOption.HasntValue() || newOption.Value.ToString().IsNullOrEmpty())
{
continue;
}
newEagerLoadings.Add(newOption);
}
}
var predicateList = predicateItem.Value;
var funcType = predicateItem.Value.First().InvokeMethod("Compile", true).GetType();
var newInstanceOfThis = GetInstanceOfService(funcType.GenericTypeArguments[0], Db);
foreach (var predicate in predicateList)
{
// A fim de tentar evitar bugs de StackOverflow
GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced);
var expandedPredicate = typeof(Extensions).InvokeStaticGenericMethod(funcType, "Expand", predicate);
var selectResponse = (IEnumerable)newInstanceOfThis.InvokeGenericMethod(predicateItem.Key,
"Many", expandedPredicate, newEagerLoadings.ToArray());
var listOfItems = selectResponse.ToList();
// Obtém o retorno
// Executa a query e preenche PredicateEntities
NavigationEntities = NavigationEntities.AddRange(listOfItems);
}
}
// Loop nas entidades para atribuir as entidades associadas
foreach (var entity in entities)
{
// Loop nas propriedades de navegação, para listar as entidades associadas
foreach (var navigationProperty in NavigationProperties)
{
// navigationProperty é a entidade associada que será atribuída a entity
var propertyInfo = entity.GetType().GetProperty(navigationProperty.Name);
var propertyType = propertyInfo.PropertyType;
var propertyValue = propertyInfo.GetValue(entity);
if (propertyValue == null)
{
continue;
}
var idPropertyInfo = propertyType.GetProperty("Id");
var keyValue = idPropertyInfo.GetValue(propertyValue);
if (keyValue == null)
{
continue;
}
var key = Convert.ToInt32(keyValue);
// Pega a lista de entidades associadas que sejam do mesmo tipo da propriedade de navegação
var associatedEntitiesOfSameType = NavigationEntities.Where(p => p.GetType() == propertyType)
.ToList();
if (!associatedEntitiesOfSameType.Any())
{
// O usuário não solicitou EagerLoading dessa navigationProperty
continue;
}
// Busca a entidade associada pelo Id, alimentado em "InternalMapEntity"
var associatedEntityInstance =
associatedEntitiesOfSameType.FirstOrDefault(
p => Convert.ToInt32(idPropertyInfo.GetValue(p)) == key);
if (associatedEntityInstance == null)
continue; // Não localizada. Removida do banco de dados?
// Atribui a entidade associada a "entity"
propertyInfo.SetValue(entity, associatedEntityInstance);
}
}
}
}
exec部分。我找到了很多例子，但没有一个有exec部分。 - Brendan Vogt