Entity Framework query

Question

I ran across this code in one of our Entity Framework applications. I know there has to be a better (more efficient) way than the three queries this code executes. Although, I can't quite get the syntax right. (I am still learing Entity Framework myself..)

There are two tables involved. This is a simple parent/child relationship.

There is a Call table that contains information about all of our Calls and there is a Units table that contains the individual serial numbers (units) assigned to each call. Note, it isn't a MANY-TO-MANY relationship. The Units table can/will contain duplicate records (Serial Numbers)!!

A call can have 0-Many children records in the Units table.

So, when a caller calls in, our Cust Rep enters a Serial Number (always creates a new record in the Units table), which associates it with this call. At that point we populate a "Call History" tab. This tab is built by the query below. (Search the Units table and finds all Units that match this Unit and then returns all Calls that are assigned to all of those Units (records).)

To summarize. The objective of the query is: based on the callID, find ANY other calls in the database that are also tied to ANY of the serial numbers that are assigned to this call.

Considering the Entity Framework created a Navigation in the tblCall table called "Categories" and in the tblCategory table called "Call", there has to be a better/more efficient way to write this query. I would really like to refactor it. :)

Here is the existing query:

    //First, get all the Serial Numbers assigned to this Call.
    var serials = from units in context.tblUnits
                where units.callID == callID
                select units.unitSerialNumber;

    List<string> serialsList = serials.ToList<string>();

    //Get all of the Call IDs that are assigned to any of the serial numbers from the list above
    var callIDs = from units in context.tblUnits
                    where serialsList.Contains(units.unitSerialNumber)
                    select units.callID;

    List<int> callIDList = callIDs.ToList<int>();

    //Return all of the calls that are in the callID list from above
    var data = from calls in context.tblCalls
                where callIDList.Contains(calls.callID)
                select calls;

    result = data.ToList<tblCall>();

Any advice is much apprecaited!

Thanks for your help Daniel. Here is the final query:

var query = (from u1 in context.tblUnits
             join u2 in context.tblUnits on u1.unitSerialNumber equals u2.unitSerialNumber
             join c in context.tblCalls on u2.callID equals c.callID
             where u1.callID == callID
             select c).Distinct();

result = query.ToList();

Answer 1

I think you could replace it with a query similar to this:

var query = from u1 in context.tblUnits
        join u2 in context.tblUnits on u1.unitSerialNumber equals u2.unitSerialNumber
        join c in context.tblCalls on (u2.callID ?? -1) equals c.callID
        where u1.callID == callID
        select c;

var result = query.ToList();