CODEWITHSUNDEEP
c#linqnhibernate
John Kattenhorn
John Kattenhorn

Reputation: 839

Linq query nhibernate; not supported exception

I'm fairly new to nHibernate having come from an EF background and I'm struggling with the following query :

_patientSearchResultModel = (from patient in _patientRepository.Query(patientSearch.BuildPatientSpecification())
                             join admission in _admissionRepository.Query(patientSearch.BuildAdmissionSpecification())
                                 on patient.Id equals admission.Patient.Id
                             orderby admission.AdmissionDate
                             select new PatientSearchResultModel(patient.Id,
                                 admission.Id,
                                 false,
                               _phaseTypeMapper.GetPhaseTypeModel(admission.PhaseType),
                                 patient.Last, patient.First,
                                 admission.InPatientLocation,
                                 admission.AdmissionDate,
                                 admission.DischargeDate,
                                 admission.RRI,
                                 null,
                                 admission.CompletionStatus,
                                 admission.FollowupStatus)).ToList();

The intent of this query is to allow users to filter the two queries on parameters built up using the two Build???Specification functions and return the resultset. There could be many admission records and I would only like one PatientSearchResultModel per patient object, with the admission object being the newest one by Admission Date.

These objects are coming from nHibernate and it keeps return a Not Supported exception. There is also an association between Patient and Admissions thus : Patient.Admissions but i couldn't figure out how to then add the query filters return from the function Build???Specifications.

I'd be really grateful if someone could point me in the right direction; am I up against the Linq provider implementation here in nHibernate and need to move to Criteria or is it my Linq query ?

If anyone has any links or suggestions for good books or other learning materials in this area that would also be really helpful too.

Upvotes: 1

Views: 6512

Answers (4)

Victor Gelmutdinov
Victor Gelmutdinov

Reputation: 589

This query is too complex to describe it using Linq. It would give wrong result finally (if Patient has more than one admission records, result would have duplicate entries).

I see two steps for solution:

1) At development stage, use in-memory query. So, take Patients using ToList() first (query db at this moment). Some predicates (Patient filter like MRN, First, Last) could be used at this stage. And then do search in-memory. Not performance, but working solution. Mark it for refactor to optimize later.

2) Finally, use NHibernate IQuery (ISQLQuery) and build sql query manually to make sure it would work as expected and work fast enough on SQL Server side. This is just read-only query and do not require Nhibernate query engine (Linq to Nhibernate) at all.

Upvotes: 0

Paul Turner
Paul Turner

Reputation: 39685

As with most popular LINQ-to-Database query providers, NHibernate will try to translate the whole query into a SQL statement to run against the database. This requires that all elements of your query are possible to express in the SQL flavour you're using.

In your query, the select new statement cannot be expressed in SQL, because you're making a call to the constructor of your PatientSearchResultModel class and are making a call to a GetPhaseTypeModel method.

You should restructure your query to express what you want to execute on the SQL database, then call AsEnumerable() to force the remainder of the query to be evaluated in-memory. After that call, you can call the constructor of your class and any .NET methods, and they will be executed as native code.

Upvotes: 0

JeffreyABecker
JeffreyABecker

Reputation: 2743

I see several potential problems:

  1. If you're using NHibernate 2.x + Linq2NHibernate explicit joins like that are not supported; in other versions they're just considered a smell.
  2. I dont think NHibernate supports calling parameterized constructors in select clauses
  3. I'm very sure NHibernate does not support calling instance methods in the select lambda

I'd suggest using the lambda syntax and SelectMany to alleviate potential join issues. Points #2 & #3 can be solved by projecting into an anonymous type, calling AsEnumerable then projecting into your model type.
Overall I'd suggest restructuring your code like:

var patientSpec = patientSearch.BuildPatientSpecification();
var admissionSpec = patientSearch.BuildAdmissionSpecification();
_patientSearchResultModel = _patientRepository.Where(patientSpec)
    .SelectMany(p=>p.Admissions).Where(admissionSpec)
    .Select(a=> new {
        PatientId = a.Patient.Id,
        AdminssionId = a.Id,
        a.PhaseType,
        a.Patient.Last,
        a.Patient.First,
        a.InPatientLocation,
        a.AdmissionDate,
        a.DischargeDate,
        a.RRI,
        a.CompletionStatus,
        a.FollowupStatus
    }).AsEnumerable()
    .Select(x=> new PatientSearchResultModel(x.PatientId, x.AdmissionId ...))
    .ToList();

Upvotes: 4

Euphoric
Euphoric

Reputation: 12849

Divide your query into parts and check which part runs and which doesn't.

My take on this is that select new ... is not supported in Linq to nHibernate.

I would recomend using something else, because it is simply too imature and feature-less to use seriously.

Upvotes: 0

Related Questions