• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1330
  • Last Modified:

FirstOrDefault() in Where-Statement (nhibernate, linq)

Hi everybody

I got this project-class with a list of status (already in the right order)

public class Project
    {

        public virtual int Id { get; set; }
        public virtual IList<ProjectStatus> Status { get; set; }
    }

public class ProjectStatus
    {
        public virtual int Id { get; set; }
        public virtual Project Project { get; set; }
        public virtual int Type { get; set; }
        public virtual DateTime Creationdate { get; set; }
    }

Open in new window


the mapping is like this

public ProjectMapping()
        {
            Table("Projects");
            Id(x => x.Id, "ProjectId");
            HasMany(x => x.Status).Table("ProjectStatus").KeyColumn("ProjectId").OrderBy("Creationdate DESC").Cascade.All();
            
        }

public ProjectStatusMapping()
        {
            Table("ProjectStatus");
            Id(x => x.Id, "ProjectStatusId");

            Map(x => x.Creationdate);
            Map(x => x.Typ);

            References<Project>(x => x.Project, "ProjectId");
        }

Open in new window


Now I would like to search a project where the current status (the first in the list) has Type = 2. I thought I could easily handle this like this:

_projectRepository.Table.Where(p => p.Status.FirstOrDefault() != null && p.Status.Type == 2).FirstOrDefault();

Open in new window


But this just throws following exception (unfortunately I only got the german version of it)

Eine Ausnahme vom Typ "Antlr.Runtime.NoViableAltException" wurde ausgelöst. [.Count[OSS.Domain.Project](.Where[OSS.Domain.Project](NHibernate.Linq.NhQueryable`1[OSS.Domain.Project], Quote((g, ) => (Equal(Convert(.FirstOrDefault[OSS.Domain.ProjectStatus](g.Status, ).Type.Id), p1))), ), )]

Does anyone know how to fix this?

Thank you very much!
0
innovasoft
Asked:
innovasoft
  • 4
  • 2
1 Solution
 
innovasoftAuthor Commented:
I figured out, that I can get a status with Type==2 by using "Any()"

_projectRepository.Table.Where(p => p.Status.Any(s => s.Type == 2)).ToList();

Open in new window


But this just returns any status with Type==2 and not only the latest one. So I tried this

_projectRepository.Table.Where(p => p.Status.Any(s => s.Type == 2 && s.Id == p.Status.Max(max => max.Id))).ToList();

Open in new window


But this fails again :(
0
 
käµfm³d 👽Commented:
Well this logic shouldn't even compile:

_projectRepository.Table.Where(p => p.Status.FirstOrDefault() != null && p.Status.Type == 2).FirstOrDefault();

Open in new window


...and even if it had it wouldn't have produced expected results, unless there was only one element in the collection. The reason is that the "p.Status.FirstOrDefault()" would return the first element of the collection--regardless of what Type it had--and the "p.Status.Type == 2"--barring the fact that the syntax is incorrect--would be independent of the earlier "p.Status.FirstOrDefault()".

Does you newest query (the bottom one) raise an exception, or does it just not return expected results?
0
 
innovasoftAuthor Commented:
thank you for your answer!

ups... I made a mistake when I put the code in the textfield (i changed some variable-names so that you can understand it better). But it actually looks like this

_projectRepository.Table.Where(p => p.Status.FirstOrDefault() != null && p.Status.FirstOrDefault().Type == 2).FirstOrDefault();

Open in new window


I'm sorry for that

and this still throws the same exception as I wrote in the first post
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
käµfm³d 👽Commented:
I'm not sure I can be of further assistance since I am not familiar with NHibernate. When looking up the details of a NoViableAltException it seems the causes vary. From what I can see, it sounds like you might be using an invalid construct as far as NHibernate is concerned. To me, it seems akin to using foreign functions in LINQ-to-SQL (e.g. trying to use DateTime.Parse within a query where there is no equivalent SQL function to DateTime.Parse). The only thing in the query that seems plausible is the FirstOrDefault, but I think it would be weird that that particular function would be unsupported.

Certainly hold out for responses from the other experts  : )
0
 
innovasoftAuthor Commented:
anyway, thank you very much for your help, kaufmed!

Now I solved the problem with a solution that I don't really like. As you said, the problem occurs because linq-to-sql can't translate the FirstOrDefault() method (at least it seems so)

No I first get a list of my projects and afterwards I filter the list

IList<Project> projects = _projectRepository.Table.ToList();
var project = projects.Where(p => p.Status.FirstOrDefault() != null && p.Status.FirstOrDefault().Type == 2).FirstOrDefault();

Open in new window


of course, it's not so nice because the performance is adversely affected by that, since it first gets all the elements of the database before filtering them.

Hope anybody still has a better solution
0
 
innovasoftAuthor Commented:
I didn't really get a nice solution. This one is only a workaround and I wanted to let you all know about that
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now