Generally speaking, when you write LINQ to Entities the generated queries seem to work on both MS SQL and Oracle. There are some Microsoft published Known Issues and Consideration in LINQ to Entities, but we also found a number of issues that occur when targeting Oracle via the Devart provider that do not affect MS SQL.
OUTER APPLY and CROSS APPLY
By far the biggest pain when testing more complex queries is when you get one of the following exception messages:
- OUTER APPLY is not supported by Oracle
- CROSS APPLY is not supported by Oracle
If you are interested in finding out more about these commands, check out the Using T-SQL CROSS APPLY and OUTER APPLY article.
Having received one of the above errors, you may have discovered the Known Issues in SqlClient for Entity Framework article or dotConnect for Oracle Entity Framework Support Limitations.
The upshot is that you need to re-construct your LINQ queries to avoid the generation of one of these unsupported calls. This means that you need to determine exactly what part of your LINQ results in the unsupported query.
You can see the generated query by calling ToTraceString, and then comparing the result of that to your LINQ to pin down the precise problem area.
If like us you develop against MS SQL on a day-to-day basis, and then test against other databases ‘less frequently’ this issue could quickly become a pain. It would be far better to know when you have first written and run your query whether the result is database agnostic immediately!
Because of this, I created some extension methods so I could conditionally verify the generated query was going to be valid:
ObjectQuery Extension
[Conditional("DEBUG")]
public static void ValidateQuery(this ObjectQuery objectQuery)
{
if (objectQuery != null)
{
string queryString = objectQuery.ToTraceString();
if (queryString.Contains("CROSS APPLY")
|| queryString.Contains("OUTER APPLY"))
{
throw new InvalidOperationException(
@"Non-portable query detected containing
CROSS APPLY or OUTER APPLY; please rework
LINQ-ENTITIES to avoid:"
+ Environment.NewLine + queryString);
}
}
}
IQueryable<T> Extension
[Conditional("DEBUG")]
public static void ValidateQuery<T>(this IQueryable<T> queryable)
{
if (queryable != null)
{
ObjectQuery objectQuery = queryable as ObjectQuery;
if (objectQuery != null)
{
objectQuery.ValidateQuery();
}
}
}
You can of course create other extensions on IEnumerable, etc. depending on your needs.
This can be called when any query gets executed centrally in the context to ensure all queries are verified. However, it’s also useful to be able to call this earlier when you are formulating a big/complex query made up of sub-queries as it helps narrow the scope of any issue.
In our code the two main causes of these issues were having a ‘subquery with paging’ and ‘grouping methods that accept an element selector’. I was going to show some examples here, but found that IBM have a great page that lists the same issues with examples at IBM Data Server LINQ Entity Framework Limitations. From what I google, it would seem the same issue affects MySQL too.
Distinct
We had the following query that was originally LINQ-SQL, and got rewritten as LINQ-ENTITIES; looks innocent enough:
Distinct on Anonymous
var items = (from s in context.Contacts.OfType<Supervisor>()
from prs in s.PlacementRequestSupervisors.DefaultIfEmpty()
let pg = prs.PlacementRequest.PlacementGroup
where s.SchoolId == schoolId
&& (pg.DateFrom >= dateFrom && pg.DateFrom <= dateTo)
&& (blockCodeId == null ? true : pg.BlockCodeId == blockCodeId)
&& (placementGroupId == null ? true : pg.PlacementGroupId == placementGroupId)
select new
{
SupervisorId = s.ContactId,
SupervisorName = s.ContactName,
WorkPhone = s.WorkPhone,
MobilePhone = s.MobilePhone,
Email = s.Email,
PayRate = s.PayRate ?? 0
}).Distinct();
But upon running – regardless of database – we got "The 'Distinct' operation cannot be applied to the collection ResultType of the specified argument."
The reason for this is that we are trying to perform the distinct on an anonymous type and this will not work in LINQ to Entities (see here, and here).
I’ve seen that one approach people take to this is using group by, e.g.
Group by
group new
{
SupervisorId = s.ContactId,
SupervisorName = s.ContactName,
WorkPhone = s.WorkPhone,
MobilePhone = s.MobilePhone,
Email = s.Email,
PayRate = s.PayRate ?? 0
}
by s.ContactId into g
select g.FirstOrDefault());
However, this results in OUTER APPLY being generated, so not viable for anyone aiming at being database agnostic!
The solution is actually very simple, and just needs a slight rework of the original as shown below:
Working Distinct
select s).Distinct().Select(super =>
new
{
SupervisorId = super.ContactId,
SupervisorName = super.ContactName,
WorkPhone = super.WorkPhone,
MobilePhone = super.MobilePhone,
Email = super.Email,
PayRate = super.PayRate ?? 0
});
ORA-12704
Some of our queries generated an ORA-12704: character set mismatch exception. This occurs when you have a clash between say Unicode and non-Unicode. All of our database string columns are Unicode, so the issues seems to occur as a result of the provider generated query.
An example that caused this for us is:
C#
select new
{
SubjectId = subject.SubjectId,
Subject = subject.CodedMetadata.Name,
PlacementGroupId = pg.PlacementGroupId,
PlacementGroupName = pg.Name,
BlockCode = pg.BlockCode.Name, // ?? string.Empty
Commenting out the string.Empty assignment on null eliminated this issue, but meant we had to move that check/assignment to LINQ to Objects code. As we are performing other formatting after the query anyway, this is not a big deal for us…but worth being aware of.
There is a seemingly related issue where you get ORA-01790, and the solution for that is to set a property on TypedNulls to true.
C#
partial void OnContextCreated()
{
DevArt.Data.Oracle.Entity.OracleEntityProviderServices.TypedNulls = true;
}
I’ve not yet had chance to test this, as our Devart trial period just expired, and our refactoring had eliminated this as an issue anyway.
Print | posted on Friday, 24 September 2010 11:59 AM