Help troubleshooting LINQ query

Question

I have this LINQ query:

 var returnList = from TblItemEntity item in itemList
                     join TblClientEntity client in clientList
                     on item.ClientNo equals client.ClientNumber
                     join TblJobEntity job in jobList
                     on item.JobNo equals job.JobNo
                     where item.ClientNo == txtSearchBox.Text //Is this filter wrong?
                     orderby client.CompanyName
                     select new { FileId = item.FileId, CompanyName = client.CompanyName, LoanStatus = item.LoanStatus, JobNo = job.JobNo, JobFinancialYE = job.JobFinancialYE, VolumeNo = item.VolumeNo };

Why doesn't this return anything?

P/S : All of them are of string datatype.

Answer 1

Have you tried to remove parts of the join to figure out where the problem is and then add those removed parts back again one after one? Start with:

var returnList = from TblItemEntity item in itemList
                 where item.ClientNo == txtSearchBox.Text //Is this filter wrong?
                 select new { FileId = item.FileId };

Since you're doing inner joins there could be that one of the joins filters out all the items.

EDIT: When debugging don't expand the return type, the select new {FileId = item.FileId} is all you need to debug.

Answer 2

Why doesn't this return anything?

There two possibilites:

1) The join is empty, that is, no items, clients and jobs have matching ID's.

2) The where clause is false for all records in the join.

To troubleshoot this you will have to remove the where clause and/or some of the joined tables to see what it takes to get any results.

Answer 3

Still waiting on that sample data.

You say you're getting results filtering by other attributes so why should this be any different? Assuming the user-input txtSearchBox has a reasonable value, try printing the values out onto the debug console and see if you're getting reasonable results. Look at the output window. Try this version of your query:

Func<string,bool> equalsSearch = s =>
{
    var res = s == txtSearchBox.Text;
    Debug.WriteLine("\"{0}\" == \"{1}\" ({2})", s, txtSearchBox.Text, res);
    return res;
};
var returnList = from TblItemEntity item in itemList
                 join TblClientEntity client in clientList
                     on item.ClientNo equals client.ClientNumber
                 join TblJobEntity job in jobList
                     on item.JobNo equals job.JobNo
                 //where item.ClientNo == txtSearchBox.Text //Is this filter wrong?
                 where equalsSearch(item.ClientNo) //use our debug filter
                 orderby client.CompanyName
                 select new { FileId = item.FileId, CompanyName = client.CompanyName, LoanStatus = item.LoanStatus, JobNo = job.JobNo, JobFinancialYE = job.JobFinancialYE, VolumeNo = item.VolumeNo };