CODEWITHSUNDEEP
c#entity-frameworklinq
Pete
Pete

Reputation: 713

How to run linq query with select when a primary key column returns null and thus the related attribute is null

I have the following query:

    var ComplaintsQuery = _context.TblComplaints.Select(p => new
    {
        ComplaintId = p.ComplaintId,
        ClientId = p.ClientId,
        ClientFullName = p.Client.ClientFullName,
        Datecomplaintreceived = p.Datecomplaintreceived,
        StaffmemberId = p.StaffmemberId,
        StaffFullName = p.Staffmember.StaffFullName,
    }
    )?.AsQueryable();

The database table for complaints is such that in some rows, clientId is null, thus I am am unable to retrieve ClientFullName, similarly for StaffFullname and StaffmemberId.

Is there any way around this to have the query return null rather than a null exception which breaks the code?

Upvotes: 0

Views: 197

Answers (3)

Nubaid Anwer
Nubaid Anwer

Reputation: 26

Well if you use EntityFramework Core it won't give you exception on null but if you are using EntityFramework you should use ? after your expected null object like Client or StaffMember rather than the whole query

var ComplaintsQuery = _context.TblComplaints.Select(p => new
    {
        ComplaintId = p.ComplaintId,
        ClientId = p.ClientId,
        ClientFullName = p.Client?.ClientFullName,
        Datecomplaintreceived = p.Datecomplaintreceived,
        StaffmemberId = p.StaffmemberId,
        StaffFullName = p.Staffmember?.StaffFullName,
    }
    ).AsQueryable();

Upvotes: 0

Pete
Pete

Reputation: 713

Thanks to Jon Skeet's comment, the following code is functional with no null exception outputs.

        var ComplaintsQuery = _context.TblComplaints.Select(p => new
        {
            ComplaintId = p.ComplaintId,
            ClientId = p.ClientId,
            ClientFullName = p.ClientId == null ? null : p.Client.ClientFullName,
            Datecomplaintreceived = p.Datecomplaintreceived,
            StaffmemberId = p.StaffmemberId,
            StaffFullName = p.StaffmemberId == null ? null : p.Staffmember.StaffFullName,
        }
        )?.AsQueryable();

Upvotes: 1

Xavier
Xavier

Reputation: 1430

Try

var ComplaintsQuery = (from p in _context.TblComplaints
                               select new
                               {
                                   ComplaintId = p.ComplaintId,
                                   ClientId = p.ClientId,
                                   ClientFullName = p.ClientId == null ? null : p.Client.ClientFullName,
                                   Datecomplaintreceived = p.Datecomplaintreceived,
                                   StaffmemberId = p.StaffmemberId,
                                   StaffFullName = p.Staffmember == null ? null : p.Staffmember.StaffFullName,
                               }).AsQueryable();

Upvotes: 0

Related Questions