Child collection not filtering even though linq to sql join exists

Question

I have a LINQ query:

var result = from mt in MessageTypes
      join mtfmt in MessageTypeField_MessageTypes
       on new { MessageTypeID = mt.ID, MessageTypeFieldID = messageTypeFieldId } equals new { MessageTypeID = mtfmt.MessageTypeID, MessageTypeFieldID = mtfmt.MessageTypeFieldID }
      where (mt.StatusID == (int)status)
      select mt;

Or Lambda syntax if you prefer (the one I am using) (messageTypeFieldID is set above the var call from a param.):

var messageTypes = context.MessageTypes
                .Join(
                context.MessageTypeField_MessageTypes,
                mt =>
                new
                    {
                        MessageTypeID = mt.ID,
                        MessageTypeFieldID = messageTypeFieldID
                    },
                mtfmt =>
                new
                    {
                        MessageTypeID = mtfmt.MessageTypeID,
                        MessageTypeFieldID = mtfmt.MessageTypeFieldID
                    },
                (mt, mtfmt) =>
                new
                    {
                        mt = mt,
                        mtfmt = mtfmt
                    }
                )
                .Where(x => (x.mt.StatusID == (int)status))
                .Select(x => x.mt);

I've just started learning LINQ joins and approached a situation where it is required in a new normalization (many-many) table I am setting up and I wish to return all message types each with their associated field which lives under 'MessageTypeField_MessageType'.

My 'MessageTypeField_MessageTypes' table is a fairly simple normalization strategy setup like this and I should state even though MessageTypeID 'has many' it is unique data so it could be:

[ID | MessageTypeID | MessageTypeFieldID]
 1    63      10
 1    63      11
 1    63      12

Now the above code executes and returns a query I am happy with.. (performs correct INNER JOIN) but when I look at messageTypes.ToList()[0].MessageTypeField_MessageTypes for example with quick watch, where I would expect to see 1 record, I am getting an entire stack of messageTypeField_MessageType records ~17, only filtered by MessageTypeFieldID and not by MessageTypeID as well. It should filter by the message type id on each iteration and only return me just the one record. Any ideas on what I am doing wrong, or how I can achieve what I need?

Sorry for the complicated example, but I hope you guys can help!

Answer 1

If you are looking at a navigation property (i.e. some child collection of mt), then this is unrelated to your query; it sounds like you want to use AssociateWith. This largely replaces the join if you are just trying to filter the child data...

Answer 2

Consider changing your select to:

 select new 
 {
    MessageType = mt,
    MessageField = mtfmt
 }

As Marc mentioned, changing the query is unrelated to the navigation properties. Something similar to the above is probably what you intended.

If you want to stick with the navigation properties, you should use a combination of AssociateWith and LoadWith. The first to filter, and the later to make it an eager load (so you don't end with multiple round trips).