How to LINQ Join when one key is an ArrayOfInt and the other an int

Question

I'm trying to get a list of messages from the database where one of the recipients of the message matches a user. Normally, if there was only one recipient, you would have something like the following

var res = db.messages.Where(m => m.id == message_id)
               .Join(db.persons, m => m.recipients, p => p.id, (m, p) => new {m, p})
               .Select(x => new Message(){ msg = x.m, person = x.p})

But what if recipients is a comma seperated string of integers and id is an integer?

Answer 1

Option 1 - using Contains:

var res = from m in db.messages
          where m.id == message_id
          from p in db.persons
          where m.recipients.Split(",").Select(i => Int32.Parse(i))
                .Contains(p.id)
          select new Message() {
              msg = m,
              person = p
          };

The idea:

1. Get the messages as in your original query
2. Get all persons where contained in the recipient list
3. Continue with your original query

---

Option 2 - using a LINQ join (maybe more complicated than it needs to be):

var res = from m in db.messages
          where m.id == message_id
          from r in m.recipients.Split(",")
          select new {
              Message = m,
              Recipient = Int32.Parse(r)
          } into rec
          join p in db.persons on rec equals p.id
          select new Message () {
              msg = m,
              person = p
          };

The idea:

1. Get the messages as in your original query
2. Split the string into a list of Int32
3. Join this list against db.persons
4. Continue with your original query

---

Not sure which of these is faster, you'll have to check.

I would also shy away from using comma-delimited strings as a foreign key reference namely because of the troubles you're having here. They're ugly to look at and a true pain to manipulate. Instead, if you have control over the schema, consider a one-to-many relation table having MessageId and PersonId. Of course if you don't have control, you're stuck.

Disclaimer: These are untested, so may need some tweaking for the code to work. The algorithms however should be ok.

Answer 2

You would need to convert recipients into a list of elements as a start. I'm assuming that recipients is a list of ids from the person table. As such from your question you have to pass in the person id to do a select on it?

var messages=db.messages.Select(
        m => m.id == message_id && 
        (m.recipients.Split(",").
            Any(recipient => reipient == person_id)
        )

var person = db.Persons.Select(p => p.id == person_id)

Note that doing this in linq is going to suffer a performance penalty as things like .Split are C# and will not work on IQueryable. As such the DB will have to transmit up a lot of data to perform this query depending on the size of your table. If you have a view on the database when you have tokenized this out, or you are capable of creating a new table in the DB where the recipients of a message are listed with a message ID, you could do this much more easily (not to mention normalising your DB in the process).