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?

Answer 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
    }