Issues with .NET Linq statement

Question

Linq and Query's syntax is one of my weakest skills. I'm having issues with getting the desired result.

I have two tables/ collections. One is filled with DocumentTypes, the other with Notifications. These are the fields they hold that matter, I omitted the ones that don't.

DocumentTypes

ID
Name
SupplierID

Notifications

ID
DocumentTypeID
UserID
ProductID
Last_Sequence

I have three parameters; userID, supplierID and productID.

I need the supplierID to get a list of all the DocumentTypes tied to that supplier. Then I need the userID and ProductID to get a list of notifications tied to those.

Then I need to join those two lists, every notification will have a documentTypeID to which it is linked. When there is a notification for a certain document type, it needs to include the fields Last_Sequence and make a new bool field that is set to true.

When there is no notification, the Last_sequence can be left empty and the bool is created and set to false.

So the result would be a list with objects that have these types.

DocumentTypeID
DocumentTypeName
BoolField (true of there is a notification tied to this one)
NotificationID (only if there is one)
Last_sequence (only if there is one)

What I have so far.

The previous version only needed the bool field added, along with the documentType information. For that I had this statement, but I can't seem to add to that what I need:

List<DocumentTypeNotification> docTypes = repository.Get<Domain.DocumentType>().Where(d => d.SuppID == SuppId).Select(d => new DocumentTypeNotification
            {
                DocTypeID = d.Id,
                DocTypeName = d.Name,
                Subscribed = notifications.Any(n => n == d.Id)
            }).ToList();

What I have tried for the new one is this, but it only gives data back, when there is a notification tied. When there isn't one, it doesn't give back that documentType data.

var temptest = from notif in repository.Get<Domain.Notification>()
                           join doctype in repository.Get<Domain.DocumentType>() on notif.DocTypeId equals doctype.Id
                           select new DocumentTypeNotification { DocTypeID = doctype.Id, DocTypeName = doctype.Name, Subscribed = true, NotifID = notif.Id, last_sequence =  notif.Last_Sequence};

EDIT: here is an example of something I tried, but does not work. The issue here is that n does not exist when I try to do n.last_sequence.

List<DocumentTypeNotification> docTypes = repository.Get<Domain.DocumentType>().Where(d => d.SuppID == SuppId).Select(d => new DocumentTypeNotification
            {
                DocTypeID = d.Id,
                DocTypeName = d.Name,
                Subscribed = notifications.Any(n => n == d.Id),
                last_sequence = test.Where(n => n.DocTypeId == d.Id).Select(n.Last_Sequence).FirstOrDefault()
                //from n in test
                                //where n.DocTypeId == d.Id
                                //select n.Last_Sequence
            }).ToList();

I was wondering how I should solve this. Do I need to make a collection of all the right DocumentTypes first, and then join that with the new collection that I made, or is there a better way to solve this?

Can you make a working/non working example at dotnetfiddle.net ? — Sarvesh Mishra
– Sarvesh Mishra, Commented Apr 27, 2017 at 9:58
That would take a lot of time as i need to make classes for notifications and Documenttypes. But I added the question to include code that I tried but doesn't work. — Robin
– Robin, Commented Apr 27, 2017 at 10:02
"That would take a lot of time" - that's time you should spend. Constructing a minimal reproducible example is part of the diagnostic work of a software engineer. — Jon Skeet
– Jon Skeet, Commented Apr 27, 2017 at 10:06

jitender · Accepted Answer · 2017-04-27 10:17:19Z

1

What about left join

 from  d in repository.Get<Domain.DocumentType>()
    join n in repository.Get<Domain.Notification>()
       on d.Id equals n.DocTypeId
    into temp
    from notific in temp.DefaultIfEmpty()
    where  d.SuppID == SuppId
    select new
    {
        d.Name,
        last_sequence = notific != null ? notific.Last_Sequence : null,
        Subscribed  = notific != null
    }

answered Apr 27, 2017 at 10:17

jitender

10.4k1 gold badge22 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Robin Over a year ago

Yes, I'm trying to work with it. One error I still have is with the notific != null ? notific.Last_Sequence : null, part.

Robin Over a year ago

The error states: type of conditional expression cannot be determined because there is no implicit conversion between 'int' and '<null>'

Robin Over a year ago

fixed it by making it default to 0 instead of null

Robin Over a year ago

This seems to be working so far, I'm going to test it more, and come back in a few hours. To mark this as the answer. Thanks!

Ruben Vardanyan · Accepted Answer · 2017-04-27 10:39:52Z

There is no concrete code samples, so at first I will define some variables. Let's suppose that we have list of document types with name documentTypes and list of notifications with name notifications. If I am understanding your problem right, than the following linq query will do what you want

documentTypes.Where(d => d.SupplierID == SuppId)
    .GroupJoin(notifications, 
                d => d.ID, 
                n => n.DocumentTypeID, 
                (document, notification) => new {document, notification}
    )
    .Select(join =>
    {
        var anyNotification = join.notification.FirstOrDefault();
        return new
        {
            DocTypeID = join.document.ID,
            DocTypeName = join.document.Name,
            Subscribed = join.notification.Any(),
            NotificationID = anyNotification != null ? anyNotification.ID : null,
            Last_sequence = anyNotification != null ? anyNotification.Last_Sequence: null,
        };
    }).ToList();

Collectives™ on Stack Overflow

Issues with .NET Linq statement

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related