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 CLOB
s 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());
}
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.
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).