LINQ - select query in related objects Entity Framework (EF4)

Question

For the following (I'm using EF4) I need to select all messages in a Thread (ContactThreadId) that were NOT yet read given LoginId

So, i based on ContactThreadId and LoginId I need to know if this LoginId has already read all messages in a Thread. If not I need to update ContactReadState with all messages from the thread with his/her LoginId and when she/he read it.

I've tried these but am stuck:

// Update read state
var thread = this.contactMessageThreadRepository.GetRow(id);
var loginEntity = this.loginRepository.GetRow(ProfileContext.LoginId);

var unreadMsg = loginEntity.Contact
  .Where(x => x.ContactThread.Any(y => y.ContactThreadId == id))
  .Select(b => b.ContactMessage.FirstOrDefault())
  .Where(q => q.ContactReadState.Count() == 0);

var unreadMsg = loginEntity.Contact
  .Where(x => x.ContactThread.Any(y => y.ContactThreadId == id))
  .Where(y => y.ContactReadState.Any(q => q.ContactId != loginEntity.ContactId));

var msg = thread.Contact
  .Where(x => x.LoginId == loginEntity.LoginId)
  .Where(y => y.ContactReadState.Count() == 0);

please help. thanks

EDIT:

Login - this table hols logins/admin records

Contact - this table holds all contacts (including Login items because Logins can also participate in a conversation-can reply to messages etc.) When a LoginId in a contact table is NOT NULL, it means it's a Login user otherwise it's a public user submitting a message.

QUESTION -> Each Login can have only 1 Contact record (I have a trigger that creates a Contact record upon Login creation). How do I make it so that it's 1 to 1.?? I've added a FK from Contact to Login table but for some reason EF created 0..1 -> * association. And it should be 1 to 1. For a given Login there can be only 1 Contact with that LoginId.

Whenever Login is viewing a list of messages a new record is inserted to ContactReadState for that Login (marking a message read by that Login (with ContactId for that Login).

Answer 1

I have a fragment, but can't put it completely together. Especially I don't know how you would set the ContactId in the new ContactReadState objects (see ??? below) because apparently a given loginId can have many contacts. So, which one to set in the new read states? Probably I misunderstood something in your model, because all those relationships look very complicated to me.

...based on contactThreadId and loginId...

I'm using a context directly, you need to translate this into your repository structure:

using (var context = new MyContext())
{
    var unreadMsgs = context.ContactMessages
        .Where(cm => cm.ContactThreadId == contactThreadId
                  && !cm.ContactReadState
                         .Any(crs => crs.Contact.LoginId == loginId))
        .ToList();
    // These should be the unread messages you want to select.

    // Now, updating ContactReadState:
    foreach(var unreadMsg in unreadMsgs)
    {
        var newContactReadState = new ContactReadState
        {
            ContactMessageId = unreadMsg.ContactMessageId,
            ContactId = ???,
            ReadDate = DateTime.Now
        };
        context.ContactReadStates.AddObject(newContactReadState);
    }

    context.SaveChanges();
}

Edit

If I understand correctly there can be contacts without login but if there is a login it is uniquely assigned to a contact.

Creating such a one-to-one relationship in EF correctly requires the following:

• You must remove the LoginId column and the corresponding relationship from your Contact table (it's the reason for the one-to-many relationship EF creates).
• The LoginId primary key column in the Login table must not be an autogenerated identity.
• You must create a foreign key relationship between Contact and Login table where the foreign key column in Login is the primary key column LoginId at the same time. So, the relationship is between Login.LoginId (the "dependent" with the FK) and Contact.ContactId (the "principal" with the PK).

This would mean that a Login with LoginId=x refers to a Contact with ContactId=x (same value) which finally makes it easy to fill the ??? in the code snippet above: ??? is simply = loginId.

Edit 2

...and of course you can replace then in the query ...

.Any(crs => crs.Contact.LoginId == loginId)

...by...

.Any(crs => crs.ContactId == loginId)

(which avoids an unnessacary join to the Contact table)