How to convert UNION ALL to OR in Linq to SQL expression trees (Oracle ORA-00932: inconsistent datatypes: expected got CLOB)
c# entity-framework-6 linq linq-to-sql oracle
Question
I am working with EF6 and am using db first generated models for MSSQL and Oracle. In few places I am searching by multiple search criteria which results in UNION ALL sql generated where each query is being in it's own sub-select.
One of columns in Oracle table is CLOB and linq to sql after it wraps all selects with UNION ALL at the top of all UNIONS it calls SELECT DISTINCT
"UnionAll1"."UNIQUE_ID" AS "C1", ... which requires to compare CLOBs and fails on Oracle side.
ORA-00932: inconsistent datatypes: expected - got CLOB
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Oracle.ManagedDataAccess.Client.OracleException: ORA-00932: inconsistent datatypes: expected - got CLOB
Is there a way to remove that DISTINCT statement? How can I make this work?
UPDATE Mechanism that generates LINQ looks like this:
public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class
{
var subQueries = new List<IQueryable<T>>();
if (search != null)
{
if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
{
subQueries.Add(queryable.SearchByPolicyNumber(search));
}
if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
{
subQueries.Add(queryable.SearchByUniqueId(search));
}
if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
{
subQueries.Add(queryable.SearchByPostCode(search));
}
}
return subQueries.DefaultIfEmpty(queryable)
.Aggregate((a, b) => a.Union(b));
}
Example of specific search method:
public static IQueryable<IRequestId> SearchByRequestId<IRequestId>(this IQueryable<IRequestId> queryable, SearchModel search)
{
var interfacesToColumnNames = new Dictionary<Type, string>
{
{typeof (IRequestId<>), "requestid"},
{typeof (IRequest_Id<>), "request_id"},
};
var paramLambda = Expression.Parameter(typeof (IRequestId));
var columnLambda = Expression.Property(paramLambda, interfacesToColumnNames.Single(o => queryable.ElementType.GetInterfaces().Any(oo => oo.Name == o.Key.Name)).Value);
var lambda = Expression.Lambda<Func<IRequestId, bool>>(
Expression.Equal(columnLambda, Expression.Convert(Expression.Constant(search.RequestId), columnLambda.Type)), paramLambda);
queryable = queryable.Where(lambda);
return queryable;
}
Example where it gets called in controller:
public ActionResult QUOTE_HOUSE()
{
var onlineDocs =
this.DatabaseManager.GetEntities<QUOTE_HOUSE>().ApplySearch(Search)
.Take(10);
return View("QUOTE_HOUSE", onlineDocs.ToList());
}
Accepted Answer
Based on the additional information from the comments, the problematic queries are produced by the following procedure:
public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class
{
var subQueries = new List<IQueryable<T>>();
if (search != null)
{
if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
{
subQueries.Add(queryable.SearchByPolicyNumber(search));
}
if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
{
subQueries.Add(queryable.SearchByUniqueId(search));
}
if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
{
subQueries.Add(queryable.SearchByPostCode(search));
}
}
return subQueries.DefaultIfEmpty(queryable)
.Aggregate((a, b) => a.Union(b));
}
where I assume the supporting methods are something like this
public static IQueryable<T> SearchByPolicyNumber<T>(this IQueryable<T> queryable, SearchModel search) where T : class
{
return queryable.Where(x => predicate_using_PolicyNumber(x, search));
}
public static IQueryable<T> SearchByUniqueId<T>(this IQueryable<T> queryable, SearchModel search) where T : class
{
return queryable.Where(x => predicate_using_UniqueId(x, search));
}
public static IQueryable<T> SearchByPostCode<T>(this IQueryable<T> queryable, SearchModel search) where T : class
{
return queryable.Where(x => predicate_using_PostCode(x, search));
}
The problem is that EF translates the LINQ Union operator to SQL UNION ALL subquery with applied DISTINCT SELECT ... as you already found. I have no idea why it does it this way instead of simply translating it to SQL UNION, but actually there is no guarantee that it would work with such type of columns either.
The only way to solve the issue I see is to eliminate the Union operator by replacing it with a single Where with Or conditions. In order to do that, you have to slightly change your design.
First, extract the predicate part from the supporting methods:
public static class SearchPredicates
{
public static Expression<Func<T, bool>> ByPolicyNumber<T>(SearchModel search) where T : class
{
return x => predicate_using_PolicyNumber(x, search);
}
public static Expression<Func<T, bool>> ByUniqueId<T>(SearchModel search) where T : class
{
return x => predicate_using_UniqueId(x, search);
}
public static Expression<Func<T, bool>> ByPostCode<T>(SearchModel search) where T : class
{
return x => predicate_using_PostCode(x, search);
}
}
Then modify the main method like this:
public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class
{
var predicates = new List<Expression<<Func<T, bool>>>();
if (search != null)
{
if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
predicates.Add(SearchPredicates.ByPolicyNumber(search));
if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
predicates.Add(SearchPredicates.ByUniqueId(search));
if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
predicates.Add(SearchPredicates.ByPostCode(search));
}
if (predicates.Count == 0)
return queryable;
var parameter = predicates[0].Parameters[0];
var condition = predicates[0].Body;
for (int i = 1; i < predicates.Count; i++)
condition = Expression.Or(condition, predicates[i].Body.ReplaceParameter(predicates[i].Parameters[0], parameter));
var predicate = Expression.Lambda<Func<T, bool>>(condition, parameter);
return queryable.Where(predicate);
}
You can use any EF compatible predicate builder, here I'm building the predicate manually. The helper method used is:
public static class ExpressionUtils
{
public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
{
return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
}
class ParameterReplacer : ExpressionVisitor
{
public ParameterExpression Source;
public Expression Target;
protected override Expression VisitParameter(ParameterExpression node)
{
return node == Source ? Target : base.VisitParameter(node);
}
}
}
With all that applied, hopefully the issue will be resolved.
Popular Answer
Just figured an alternative.
return subQueries.DefaultIfEmpty(queryable)
.Aggregate((a, b) => a.Concat(b));
then after ToList() where you are actually using output .Distinct(new YourEqualityComparer())). To filter out possible duplicates.
This isn't ideal solution since requires you to manually implement IEquitable on structures, but if you are after performance it is likely to be faster. OR will require composite indexes on database while querying by two separate single column indexes then combining the data will be not require to have all possible column-index combinations on table. Another downside you are likely to use Take() before ToList() and then after filtering you might get to few records (and have to re-query).
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
