EF CanDelete: how to know if you have some FK on an entity?

Yesterday I treated an interesting case.


Before deleting an entity, my customer wanted to check if there is no foreign key on it.


A first solution, the worst one but also the one he implemented for this, is to load all entity children relationships and then to look if there is any.


This solution works but has two main issues:


  • many entities will be unnecessarily loaded
  • the developer need to know which are the entity dependent relationships…

To fix this last point we can use a T4 template. However, unnecessarily loading entities is a very bad practice.


Of course, we can load only one child entity in order to reduce the impact, however, I propose a better solution.


T4 generation uses model metadata. With ObjectContext class, we can access to these metadata and so I will use it to only get a boolean from the database.


EntityType entityType = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(TEntity).Name);
 
var navigationProperties = entityType.NavigationProperties.Where(enp =>
enp.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne ||
enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).ToList(); if (navigationProperties.Count == 0)
return true;

There are three ways to query with EF:


  • LINQ To Entity
  • ESQL
  • Query Builder

I will try all of them in this post.


Query Builder


With Query Builder querying, we use ESQL fragments in ObjectQuery class querying methods.


string any = navigationProperties.Select(np =>
{ 
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
return string.Format("EXISTS(SELECT 1 FROM it.{0})", np.Name);
return string.Format("it.{0} <> null", np.Name); }).Aggregate((exp1, exp2) => string.Format("{0} OR {1}", exp1, exp2)); List<ObjectParameter> objectParameters = new List<ObjectParameter>(); string where = entityType.KeyMembers.Select(km => {
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
objectParameters.Add(new ObjectParameter(km.Name, propertyInfo.GetValue(entity, null)));
return string.Format("it.{0} == @{0}", km.Name); }).Aggregate((k1, k2) => string.Format("{0} AND {1}", k1, k2)); return !context.CreateObjectSet<TEntity>().Where(any).Where(where, objectParameters.ToArray()).SelectValue<bool>("true").FirstOrDefault();


ESQL


It’s also possible to only generate an ESQL string.


string any = navigationProperties.Select(np =>
{ 
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
return string.Format("EXISTS(SELECT 1 FROM it.{0})", np.Name);
return string.Format("it.{0} <> null", np.Name); }).Aggregate((exp1, exp2) => string.Format("{0} OR {1}", exp1, exp2)); List<ObjectParameter> objectParameters = new List<ObjectParameter>(); string where = entityType.KeyMembers.Select(km => {
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
objectParameters.Add(new ObjectParameter(km.Name, propertyInfo.GetValue(entity, null)));
return string.Format("it.{0} == @{0}", km.Name); }).Aggregate((k1, k2) => string.Format("{0} AND {1}", k1, k2)); DbDataRecord value = context.CreateQuery<DbDataRecord>(string.Format("SELECT false FROM {0}.{1} AS it WHERE {2} AND ({3})", entityContainer.Name, entitySet.Name, where, any), objectParameters.ToArray()).FirstOrDefault(); if (value == null)
return true; return value.GetBoolean(0);

L2E


It’s also possible to generate an Expression in order to use LINQ To Entities.


ParameterExpression expressionAnyParameter = Expression.Parameter(typeof(TEntity));
 
Expression expressionAnyBody = navigationProperties.Select(np =>
{ 
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(np.Name);
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
{
return (Expression)Expression.Call(
null,
typeof(Enumerable).GetMethods().First(m => m.Name == "Any" && m.GetParameters().Length == 1).MakeGenericMethod(propertyInfo.PropertyType.GetGenericArguments()[0]),
Expression.MakeMemberAccess(
expressionAnyParameter,
propertyInfo));
}
return (Expression)Expression.NotEqual(
Expression.MakeMemberAccess(
expressionAnyParameter,
propertyInfo),
Expression.Constant(
null,
typeof(object))); }).Aggregate((exp1, exp2) => Expression.OrElse(exp1, exp2)); Expression<Func<TEntity, bool>> expressionAny = Expression.Lambda<Func<TEntity, bool>>(expressionAnyBody, expressionAnyParameter); ParameterExpression expressionWhereParameter = Expression.Parameter(typeof(TEntity)); Expression expressionWhereBody = entityType.KeyMembers.Select(km => {
Expression<Func<TEntity>> exp = () => entity;
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
ParameterExpression expressionWhereKeyParameter = Expression.Parameter(propertyInfo.PropertyType);
return Expression.Equal(
Expression.MakeMemberAccess(
expressionWhereParameter,
propertyInfo),
Expression.MakeMemberAccess(
exp.Body,
propertyInfo)); }).Aggregate((k1, k2) => Expression.AndAlso(k1, k2)); Expression<Func<TEntity, bool>> expressionWhere = Expression.Lambda<Func<TEntity, bool>>(expressionWhereBody, expressionWhereParameter); return ! context.CreateObjectSet<TEntity>().Where(expressionWhere).Where(expressionAny).Select(e => true).FirstOrDefault();

The expressionAny generation is “classic”. However, the second one (expressionWhere) is more interesting.


Indeed, in order to avoid SQL injection, I don’t want to compare my entity keys with constants but with variables to have a parameterized SQL query.


The other interesting point is the exp variable. Why do I need it?


To answer to this question, we need to understand lambdas.


With a lambda expression or an anonymous delegate, it is possible to use a variable of the parent method. This is really awesome but how does it work?


In fact, in this case, the compiler creates a new class with a public field which will be used by parent method and by the lambda.


The issue here, is the fact that I don’t know this class because it’s generated by the compiler. So my idea is to get this field using the variable exp.


Cool, it works. However, we can improve it.


First, previous versions do not work with inheritance. Indeed, we can have no entityset on the entity type.


To fix this issue without using Reflection, we can use ESQL to get an ObjectQuery<TEntity>.


ESQL


EntityType entityType = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(TEntity).Name);
EntityContainer entityContainer = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityContainer>().First();
EntitySet entitySet = null;
EntityType entityTypeLoop = entityType;
while (entityTypeLoop != null && (entitySet = entityContainer.BaseEntitySets.OfType<EntitySet>().FirstOrDefault(es => es.ElementType == entityTypeLoop)) == null)     
entityTypeLoop = (EntityType)entityTypeLoop.BaseType; if (entitySet == null)
throw new InvalidOperationException();
var navigationProperties = entityType.NavigationProperties.Where(enp =>
enp.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne ||
enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).ToList(); if (navigationProperties.Count == 0)
return true; string any = navigationProperties.Select(np => {
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
return string.Format("EXISTS(SELECT 1 FROM it.{0})", np.Name);
return string.Format("it.{0} <> null", np.Name); }).Aggregate((exp1, exp2) => string.Format("{0} OR {1}", exp1, exp2)); List<ObjectParameter> objectParameters = new List<ObjectParameter>(); string where = entityType.KeyMembers.Select(km => {
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
objectParameters.Add(new ObjectParameter(km.Name, propertyInfo.GetValue(entity, null)));
return string.Format("it.{0} == @{0}", km.Name); }).Aggregate((k1, k2) => string.Format("{0} AND {1}", k1, k2)); DbDataRecord value = context.CreateQuery<DbDataRecord>(string.Format("SELECT false FROM OFTYPE({0}.{1}, {2}) AS it WHERE {3} AND ({4})", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName, where, any),
objectParameters.ToArray()).FirstOrDefault();
if (value == null)
return true; return value.GetBoolean(0);

It’s also possible to use ESQL to generate the ObjectQuery<TEntity> and then use another way to process.


Query Builder


return !context.CreateQuery<TEntity>(string.Format("SELECT VALUE entity FROM OFTYPE({0}.{1}, {2}) AS entity", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName)).
Where(any).Where(where, objectParameters.ToArray()).SelectValue<bool>("true").FirstOrDefault();

Note the VALUE keyword in ESQL query which means that we want the whole entity and not only some columns.


L2E


return !context.CreateQuery<TEntity>(string.Format("SELECT VALUE entity FROM OFTYPE({0}.{1}, {2}) AS entity", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName)).
Where(expressionWhere).Where(expressionAny).Select(e => true).FirstOrDefault();

Another way to improve it is to cache the query generation.


My first idea is to create a static class CanDeleteImplementation<TEntity>. What is very important here is to use a generic class. So static fields will be different for two different EntityTypes.


ESQL


public static class CanDeleteImplementation<TEntity> 
where TEntity : class {
private static string _query;
private static List<Func<TEntity, ObjectParameter>> _parametersFactories;
public static bool CanDelete(ObjectContext context, TEntity entity)
{
if (_query == null)
{
_parametersFactories = new List<Func<TEntity, ObjectParameter>>();
EntityType entityType = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(TEntity).Name);
EntityContainer entityContainer = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityContainer>().First();
EntitySet entitySet = null;
EntityType entityTypeLoop = entityType;
while (entityTypeLoop != null && (entitySet = entityContainer.BaseEntitySets.OfType<EntitySet>().FirstOrDefault(es => es.ElementType == entityTypeLoop)) == null)
entityTypeLoop = (EntityType)entityTypeLoop.BaseType;
if (entitySet == null)
throw new InvalidOperationException();
var navigationProperties = entityType.NavigationProperties.Where(enp =>
enp.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne ||
enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).ToList();
if (navigationProperties.Count == 0)
return true;
ParameterExpression expressionAnyParameter = Expression.Parameter(typeof(TEntity));
string any = navigationProperties.Select(np =>
{
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
return string.Format("EXISTS(SELECT 1 FROM it.{0})", np.Name);
return string.Format("it.{0} <> null", np.Name);
}).Aggregate((exp1, exp2) => string.Format("{0} OR {1}", exp1, exp2));
string where = entityType.KeyMembers.Select(km =>
{
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
ParameterExpression parameterFactoryParameterExpression = Expression.Parameter(typeof(TEntity));
_parametersFactories.Add(Expression.Lambda<Func<TEntity, ObjectParameter>>(Expression.New(
typeof(ObjectParameter).GetConstructor(new Type[] { typeof(string), typeof(object) }),
Expression.Constant(km.Name),
Expression.Convert(
Expression.MakeMemberAccess(
parameterFactoryParameterExpression,
typeof(TEntity).GetProperty(km.Name)),
typeof(Object))), parameterFactoryParameterExpression).Compile());
return string.Format("it.{0} == @{0}", km.Name);
}).Aggregate((k1, k2) => string.Format("{0} AND {1}", k1, k2));
_query = string.Format("SELECT false FROM OFTYPE({0}.{1}, {2}) AS it WHERE {3} AND ({4})", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName, where, any);
} DbDataRecord value = context.CreateQuery<DbDataRecord>(_query, _parametersFactories.Select(p => p(entity)).ToArray()).FirstOrDefault();
if (value == null)
return true;
return value.GetBoolean(0);
} }

Query Builder


The same with Query Builder :


public static class CanDeleteImplementation<TEntity> 
where TEntity : class {
private static string _query;
private static string _any;
private static string _where;
private static List<Func<TEntity, ObjectParameter>> _parametersFactories;
public static bool CanDelete(ObjectContext context, TEntity entity)
{
if (_query == null)
{
_parametersFactories = new List<Func<TEntity, ObjectParameter>>();
EntityType entityType = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(TEntity).Name);
EntityContainer entityContainer = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityContainer>().First();
EntitySet entitySet = null;
EntityType entityTypeLoop = entityType;
while (entityTypeLoop != null && (entitySet = entityContainer.BaseEntitySets.OfType<EntitySet>().FirstOrDefault(es => es.ElementType == entityTypeLoop)) == null)
entityTypeLoop = (EntityType)entityTypeLoop.BaseType;
if (entitySet == null)
throw new InvalidOperationException();
var navigationProperties = entityType.NavigationProperties.Where(enp =>
enp.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne ||
enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).ToList();
if (navigationProperties.Count == 0)
return true;
ParameterExpression expressionAnyParameter = Expression.Parameter(typeof(TEntity));
_any = navigationProperties.Select(np =>
{
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
return string.Format("EXISTS(SELECT 1 FROM it.{0})", np.Name);
return string.Format("it.{0} <> null", np.Name);
}).Aggregate((exp1, exp2) => string.Format("{0} OR {1}", exp1, exp2));
_where = entityType.KeyMembers.Select(km =>
{
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
ParameterExpression parameterFactoryParameterExpression = Expression.Parameter(typeof(TEntity));
_parametersFactories.Add(Expression.Lambda<Func<TEntity, ObjectParameter>>(Expression.New(
typeof(ObjectParameter).GetConstructor(new Type[] { typeof(string), typeof(object) }),
Expression.Constant(km.Name),
Expression.Convert(
Expression.MakeMemberAccess(
parameterFactoryParameterExpression,
typeof(TEntity).GetProperty(km.Name)),
typeof(Object))), parameterFactoryParameterExpression).Compile());
return string.Format("it.{0} == @{0}", km.Name);
}).Aggregate((k1, k2) => string.Format("{0} AND {1}", k1, k2));
_query = string.Format("SELECT VALUE entity FROM OFTYPE({0}.{1}, {2}) AS entity", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName);
} return ! context.CreateQuery<TEntity>(_query).Where(_where, _parametersFactories.Select(p => p(entity)).ToArray()).Where(_any).SelectValue<bool>("true").FirstOrDefault();
} }

L2E


With expression generation, it’s more difficult. Indeed, we can’t access parameters.


We can naively think that this code works.


public static class CanDeleteImplementation<TEntity> 
where TEntity : class {
private static string _query;
private static Expression<Func<TEntity, bool>> _expressionAny;
private static Expression<Func<TEntity, bool>> _expressionWhere;
public static bool CanDelete(ObjectContext context, TEntity entity)
{
if (_query == null)
{
EntityType entityType = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(TEntity).Name);
EntityContainer entityContainer = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityContainer>().First();
EntitySet entitySet = null;
EntityType entityTypeLoop = entityType;
while (entityTypeLoop != null && (entitySet = entityContainer.BaseEntitySets.OfType<EntitySet>().FirstOrDefault(es => es.ElementType == entityTypeLoop)) == null)
entityTypeLoop = (EntityType)entityTypeLoop.BaseType;
if (entitySet == null)
throw new InvalidOperationException();
var navigationProperties = entityType.NavigationProperties.Where(enp =>
enp.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne ||
enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).ToList();
if (navigationProperties.Count == 0)
return true;
ParameterExpression expressionAnyParameter = Expression.Parameter(typeof(TEntity));
Expression expressionAnyBody = navigationProperties.Select(np =>
{
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(np.Name);
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
{
return (Expression)Expression.Call(
null,
typeof(Enumerable).GetMethods().First(m => m.Name == "Any" && m.GetParameters().Length == 1).MakeGenericMethod(propertyInfo.PropertyType.GetGenericArguments()[0]),
Expression.MakeMemberAccess(
expressionAnyParameter,
propertyInfo));
}
return (Expression)Expression.NotEqual(
Expression.MakeMemberAccess(
expressionAnyParameter,
propertyInfo),
Expression.Constant(
null,
typeof(object)));
}).Aggregate((exp1, exp2) => Expression.OrElse(exp1, exp2));
_expressionAny = Expression.Lambda<Func<TEntity, bool>>(expressionAnyBody, expressionAnyParameter);
ParameterExpression expressionWhereParameter = Expression.Parameter(typeof(TEntity));
Expression expressionWhereBody = entityType.KeyMembers.Select(km =>
{
Expression<Func<TEntity>> exp = () => entity;
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
ParameterExpression expressionWhereKeyParameter = Expression.Parameter(propertyInfo.PropertyType);
return Expression.Equal(
Expression.MakeMemberAccess(
expressionWhereParameter,
propertyInfo),
Expression.MakeMemberAccess(
exp.Body,
propertyInfo));
}).Aggregate((k1, k2) => Expression.AndAlso(k1, k2));
_expressionWhere = Expression.Lambda<Func<TEntity, bool>>(expressionWhereBody, expressionWhereParameter);
_query = string.Format("SELECT VALUE entity FROM OFTYPE({0}.{1}, {2}) AS entity", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName);
}
return !context.CreateQuery<TEntity>(_query).Where(_expressionWhere).Where(_expressionAny).Select(e => true).FirstOrDefault();
} }

But no! This code does not work. Indeed with it, we will always have the same result (the first one).


Another issue is the fact that Expressions are immutable.


We can generate the whereExpression each time.


We can also use a static field which references the entity.


public static class CanDeleteImplementation<TEntity> 
where TEntity : class {
private static string _query;
private static Expression<Func<TEntity, bool>> _expressionAny;
private static Expression<Func<TEntity, bool>> _expressionWhere;
private static TEntity _entity;
public static bool CanDelete(ObjectContext context, TEntity entity)
{
if (_query == null)
{
EntityType entityType = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(TEntity).Name);
EntityContainer entityContainer = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityContainer>().First();
EntitySet entitySet = null;
EntityType entityTypeLoop = entityType;
while (entityTypeLoop != null && (entitySet = entityContainer.BaseEntitySets.OfType<EntitySet>().FirstOrDefault(es => es.ElementType == entityTypeLoop)) == null)
entityTypeLoop = (EntityType)entityTypeLoop.BaseType;
if (entitySet == null)
throw new InvalidOperationException();
var navigationProperties = entityType.NavigationProperties.Where(enp =>
enp.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne ||
enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).ToList();
if (navigationProperties.Count == 0)
return true;
ParameterExpression expressionAnyParameter = Expression.Parameter(typeof(TEntity));
Expression expressionAnyBody = navigationProperties.Select(np =>
{
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(np.Name);
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
{
return (Expression)Expression.Call(
null,
typeof(Enumerable).GetMethods().First(m => m.Name == "Any" && m.GetParameters().Length == 1).MakeGenericMethod(propertyInfo.PropertyType.GetGenericArguments()[0]),
Expression.MakeMemberAccess(
expressionAnyParameter,
propertyInfo));
}
return (Expression)Expression.NotEqual(
Expression.MakeMemberAccess(
expressionAnyParameter,
propertyInfo),
Expression.Constant(
null,
typeof(object)));
}).Aggregate((exp1, exp2) => Expression.OrElse(exp1, exp2));
_expressionAny = Expression.Lambda<Func<TEntity, bool>>(expressionAnyBody, expressionAnyParameter);
ParameterExpression expressionWhereParameter = Expression.Parameter(typeof(TEntity));
Expression expressionWhereBody = entityType.KeyMembers.Select(km =>
{
Expression<Func<TEntity>> exp = () => _entity;
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
ParameterExpression expressionWhereKeyParameter = Expression.Parameter(propertyInfo.PropertyType);
return Expression.Equal(
Expression.MakeMemberAccess(
expressionWhereParameter,
propertyInfo),
Expression.MakeMemberAccess(
exp.Body,
propertyInfo));
}).Aggregate((k1, k2) => Expression.AndAlso(k1, k2));
_expressionWhere = Expression.Lambda<Func<TEntity, bool>>(expressionWhereBody, expressionWhereParameter);
_query = string.Format("SELECT VALUE entity FROM OFTYPE({0}.{1}, {2}) AS entity", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName);
}
_entity = entity;
return !context.CreateQuery<TEntity>(_query).Where(_expressionWhere).Where(_expressionAny).Select(e => true).FirstOrDefault();
} }

However, this code is not thread-safe!!!


We could use a lock around the last two instructions.


However, in my opinion, using a lock which can be long (time to execute the SQL query) is not a good idea.


So we have to regenerate the whereExpression. However, even with this approach, it’s possible to cache the Expression generation process.


public static class CanDeleteImplementation<TEntity> 
where TEntity : class {
private static string _query;
private static Expression<Func<TEntity, bool>> _expressionAny;
private static Func<TEntity, Expression<Func<TEntity, bool>>> _getExpressionWhere;


public static bool CanDelete(ObjectContext context, TEntity entity)
{
if (_query == null)
{
EntityType entityType = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(TEntity).Name);
EntityContainer entityContainer = context.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityContainer>().First();
EntitySet entitySet = null;
EntityType entityTypeLoop = entityType;
while (entityTypeLoop != null && (entitySet = entityContainer.BaseEntitySets.OfType<EntitySet>().FirstOrDefault(es => es.ElementType == entityTypeLoop)) == null)
entityTypeLoop = (EntityType)entityTypeLoop.BaseType;
if (entitySet == null)
throw new InvalidOperationException();
var navigationProperties = entityType.NavigationProperties.Where(enp =>
enp.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne ||
enp.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).ToList();
if (navigationProperties.Count == 0)
return true; ParameterExpression expressionAnyParameter = Expression.Parameter(typeof(TEntity));
Expression expressionAnyBody = navigationProperties.Select(np =>
{
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(np.Name);
if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many)
{
return (Expression)Expression.Call(
null,
typeof(Enumerable).GetMethods().First(m => m.Name == "Any" && m.GetParameters().Length == 1).MakeGenericMethod(propertyInfo.PropertyType.GetGenericArguments()[0]),
Expression.MakeMemberAccess(expressionAnyParameter, propertyInfo));
}
return (Expression)Expression.NotEqual(
Expression.MakeMemberAccess(
expressionAnyParameter,
propertyInfo),
Expression.Constant(
null,
typeof(object)));
}).Aggregate((exp1, exp2) => Expression.OrElse(exp1, exp2));
_expressionAny = Expression.Lambda<Func<TEntity, bool>>(expressionAnyBody, expressionAnyParameter); ParameterExpression entityParameter = Expression.Parameter(typeof(TEntity));
ParameterExpression expressionWhereParameter = Expression.Parameter(typeof(TEntity));
_getExpressionWhere = Expression.Lambda<Func<TEntity, Expression<Func<TEntity, bool>>>>(
Expression.Lambda<Func<TEntity, bool>>(
entityType.KeyMembers.Select(km =>
{
PropertyInfo propertyInfo = typeof(TEntity).GetProperty(km.Name);
ParameterExpression expressionWhereKeyParameter = Expression.Parameter(propertyInfo.PropertyType);
return Expression.Equal(
Expression.MakeMemberAccess(
expressionWhereParameter,
propertyInfo),
Expression.MakeMemberAccess(
entityParameter,
propertyInfo));
}).Aggregate((k1, k2) => Expression.AndAlso(k1, k2)),
expressionWhereParameter),
entityParameter).Compile();
_query = string.Format("SELECT VALUE entity FROM OFTYPE({0}.{1}, {2}) AS entity", entityContainer.Name, entitySet.Name, typeof(TEntity).FullName);
}
return !context.CreateQuery<TEntity>(_query).Where(_getExpressionWhere(entity)).Where(_expressionAny).Select(e => true).FirstOrDefault();
} }

In conclusion, even if we often forget it, LINQ To Entities is not always the best way to querying entities, particularly when we want to generate the query.


In this case, I find ESQL or Query Builder approaches better.

This entry was posted in 7671, 7674. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>