FirstOrDefault conditional always causes a failure

kerrywales · Post by **kerrywales** » Mon 14 Jun 2010 13:04

I am using FirstOrDefault with a condition for the first time. I have treied a few permutations. Always with the same result - failure.

I have Tables -
Jobs (list of Jobs)
Names (list of names able to do the jobs)
JobNames (list of name(s) that are associated with a specific job.

All Jobs have at lease one name associated with it so -
for every row in Jobs there is at least one row in JobNames

For every Job there is one Main name (person responsible). Jobnames has the columns
Surname, Forename, IsResponsible

For every job there is one row in Jobnames that has the flag IsResponsible set to true.
So if there is only one person on a job then

there is one row in jobs and one row in JobNames and the flag IsResponsible is set to true.

Devart (build 4.90.140) was used to build an entity model for queries.

Now to the problem. I want a list of Jobs to include the surname of the person responsible for the job.

The code in a query

Surname = Jobs.JobNames.FirstOrDefault().Names.Surname,

works but I cannot guarantee the that the Surname returned is that of the person responsible. Looking at examples on the web for the syntax I presumed

Surname = Jobs.JobNames.FirstOrDefault(n => n.IsResponsible == true).Names.Surname,

Fails with the errors
base {System.Data.EntityException} = {"An error occurred while preparing the command definition. See the inner exception for details."}

InnerException = {"OUTER APPLY is not supported by PostgreSQL"}

Does this mean that the issue is with Postgres and I can't do it?
Is the problem with the Devart interface and if so will there be a fix?
Is there a workround to the issue?

Many thanks

Kerry

AndreyR · Post by **AndreyR** » Mon 14 Jun 2010 15:28

Take a look at this discussion.
Unfortunately, nothing has changed.

kerrywales · Post by **kerrywales** » Mon 14 Jun 2010 23:55

No movement or workarounds in 20 months.

Disappointing.

Will try and look at the issue from another angle to get the results I want.

kerrywales · Post by **kerrywales** » Tue 15 Jun 2010 00:30

OK,
read the discussion and relooked at the code. I do note that this First doesn't seem to work when I have a one to many. I note that a request was to add to the db to make it one to one.

Devart creates all my relationships other than the one-to-one and I don't want to modify my db so I came up with

var query = from job in entity.TblJobs // Look at all the Jobs
from worker in entity.TblJobNames // Look at all the names
where job.department = varDept // Get be the list of jobs I want
where job.jobNumber == worker.jobNumber && worker.IsResponsible == true //get me the worker responsible for each job

select new
{
job...... //get all the columns I want from the other areas
job.TblJobNames.Count(), //tell me how many workers are on the job
worker.TblNames.Surname // tell me the surname of who is responsible for the job
}

Not neat, not efficient.

But it does give me the info.

Hmmmmm?

AndreyR · Post by **AndreyR** » Tue 15 Jun 2010 17:18

Thank you for sharing your solution.