How to handle null values in linq expression with joins?

Question

I try to get some data from few table with joining. This query is give an error as System.InvalidOperationException , Message=" Sequence contains no elements ". How can I avoid this error.

var assignments = (from s in _db.SubmissionLinks
                   join a in _db.Assignments on s.AssignmentID equals a.AssignmentID
                   join p in _db.Projects on a.ProjectID equals p.ID
                   join s2 in _db.SystemUsers on p.SystemUserFK equals s2.ID
                   select new AssignmentViewModel()
                   {
                        SubmissionlinkID = s.SubmissionLinkID,
                        SubmissionlinkName = s.SubmissionName,
                        ProjectID = p.ID,
                        ProjectName = p.ProjectName,
                        Deadline = s.Deadline,
                        Userid = s2.ID,
                        Assignmentid = a.AssignmentID,
                        IsActive = s.ActiveStatus
                   }).Where(s =>s.Userid == ID && s.IsActive == 0).Distinct().First();

I try to avoid that by checking returning objects inside of the where clause but it didn't work

var assignments = (from s in _db.SubmissionLinks
                   join a in _db.Assignments on s.AssignmentID equals a.AssignmentID
                   join p in _db.Projects on a.ProjectID equals p.ID
                   join s2 in _db.SystemUsers on p.SystemUserFK equals s2.ID
                   select new AssignmentViewModel()
                   {
                        SubmissionlinkID = s.SubmissionLinkID,
                        SubmissionlinkName = s.SubmissionName,
                        ProjectID = p.ID,
                        ProjectName = p.ProjectName,
                        Deadline = s.Deadline,
                        Userid = s2.ID,
                        Assignmentid = a.AssignmentID,
                        IsActive = s.ActiveStatus
                   }).Where(s => s != null && s.Userid == ID && s.IsActive == 0).Distinct().First();

by using `FirstOrDefault` instead `First` or by changing query to something that returns some data — Selvin, Apr 20 '22 at 11:43
Don't use JOINs in the first place. You're using Entity Framework, not LINQ. It's EF's job to generate the JOINs from the relations between entities. Your `Assignment` class should have a `public List Submissions` property and a `Project` property. Retrieving a single project would also retrieve assignments and submissions without any JOINs — Panagiotis Kanavos, Apr 20 '22 at 11:45
Just to reinforce what @PanagiotisKanavos said https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/ — juharr, Apr 20 '22 at 11:47

Caius Jard · Answer 1 · 2022-04-20T12:05:18.123

Agree with Panagiotis; its supposed to be that SubmissionLink has a .Assignment property that returns the parent Assignment, Assignment has a .Project prop that returns the Project etc, and then you just do:

_db.SubmissionLinks
  .Where(s =>s.Userid == ID && s.IsActive == 0)
  .Select(s => s.Select(s => new AssignmentViewModel()
   {
        SubmissionlinkID = s.SubmissionLinkID,
        SubmissionlinkName = s.SubmissionName,
        ProjectID = s.Assignment.Project.ID,
        ProjectName = s.Assignment.Project.ProjectName,
        Deadline = s.Deadline,
        Userid =  s.Assignment.Project.SystemUser.ID,
        Assignmentid = s.Assignment.AssignmentID,
        IsActive = s.ActiveStatus
   })
  .SomethingThatRunsTheQueryLike_First_FirstOrDefault_ToList_etc

EF works out the rest from how it seens you use the navigations (s.Assignment.Project.ProjectName etc). Starting from the many end and going up to the one end is quite easy, because it's just a single object at the end of the nav. Going down through collections is a bit more of a nuisance, so think carefully about where you start your query from for the easiest life. For example if you wanted the Project and its most recent submission, you might:

db.Projects.Select(p => new Something{
  p.ProjectName,
  MostRecentLinkId = p.SubmissionLinks.OrderByDescending(sl => sl.CreatedDate).First().Id
})

That p.SubmissionLinks.OrderByDescending(sl => sl.CreatedDate).First().Id would be translated (by recent EFC) to something like ... ROW_NUMBER() OVER(PARTITION BY projectid ORDER BY createddate DESC) rn ... WHERE rn=1 in a subquery, but working with collections is usually that little more tricky because you're performing aggregations to make them make sense in the context of a single parent. If you can start from a different place so youre not digging into collections it's simpler

`ToListAsync()` instead `First()` ... strange choice ... it will fix an error but it will return list instead single result ... — Selvin, Apr 20 '22 at 11:53
It's intended to demonstrate a concept of having EF form joins by using nav props, not about the specifics of giving one or many results; OP is free to use First, Where, whatever they want - it's not what the answer is driving at — Caius Jard, Apr 20 '22 at 11:57
but the problem is `First()` and nothing more his question is about error which says: *System.InvalidOperationException , Message=" Sequence contains no elements ".* ... it has nothing to do with good practices — Selvin, Apr 20 '22 at 11:58
Then write an answer that fixes their problem, give them a fish and win the points. I'm trying to educate them how to better and more simply use EF so their future life is easier and they're leveraging EF for what it was intended, rather than just being a way for them to take their SQL approach to a problem, dump it into C# and change a few keywords so they can carry on with the SQL-mindset.. — Caius Jard, Apr 20 '22 at 12:01

How to handle null values in linq expression with joins?

1 Answers1