Unable to retrieve record from join table & conditional where clause

Question

My case description: In my C# and LINQ to SQL application, I am implementing FeserWard.Controls Intellibox. For a sales of handphone, user will type in handphone's IMEI in the intellibox and the box will do search in Table Handphone to look for the user input IMEI and finally display the exact match IMEI record.

Problem: I want to filter out all the (Handphone.IMEI) with status=Available (Item.I_Status="Available"), and from there, when user typing in IMEI, the intellibox list will do search only from the Available IMEI.

SQL

    select h.HP_ID, h.IMEI, h.Colour, i.I_Status
    from Item i, Handphone h
    where i.I_ID = h.HP_ID AND i.I_Status='Available'

I want to replace IEnumerable DoSearch's LINQ with this but stuck.

    var availableIMEISearch = from i in dataContext.Items.ToList()
                              join h in dataContext.Handphones.ToList()
                              on i.I_ID equals h.HP_ID
                              where(h.IMEI.StartsWith(searchTerm, StringComparison.OrdinalIgnoreCase)) && (i.I_Status=="Available")
                              select new { i, h };
    return availableIMEISearch;

Current workable method: IEnumerable DoSearch

    DataClasses1DataContext dataContext = new DataClasses1DataContext();
    public IEnumerable DoSearch(string searchTerm, int maxResults, object extraInfo)
    {
        var imeiSearch = dataContext.Handphones.ToList()
        .Where(h => h.IMEI.StartsWith(searchTerm, StringComparison.OrdinalIgnoreCase));
        return imeiSearch;
    }

Table1:

Item (PK = I_ID)

Table2:

Handphone (PK,FK1 = HP_ID), where Item.I_ID = Handphone.HP_ID

Why are you calling `ToList` so often? That's pulling *all* the data from the table into memory, rather than doing the query in the database... bad idea. — Jon Skeet, Oct 17 '13 at 05:59
@JonSkeet : Maybe he needs to do that because of the `StringComparison.OrdinalIgnoreCase`? — Arion, Oct 17 '13 at 06:00
@Arion: I *thought* that LINQ to SQL supported that, although I could be wrong. But at *least* the join could be done in the database... and then potentially use AsEnumerable rather than ToList. — Jon Skeet, Oct 17 '13 at 06:08
@JonSkeet : Sorry my bad. It supports it. [Case insensitive string compare in LINQ-to-SQL](http://stackoverflow.com/questions/841226/case-insensitive-string-compare-in-linq-to-sql/4072011#4072011) . Yes I agree. The joins should be in the database. — Arion, Oct 17 '13 at 06:13

score 1 · Answer 1 · answered Oct 17 '13 at 06:24

As Joh Skeet(A SO Hero) mentioned, you should not use ToList in query, because it will promt query to be executed, and you will be fetching full tables data to client joining them on client site. Let Linq generate query expression with join, and send it to DB in order to make join on DB side, delayed execution. Less sideeffects.

You didn't mentioned whether you need case sensitive search of searchterm, but I assume that insensitive is much more convenient, so just remove StringComparison.

As you only need Handphones info -> better exclude i from select term, select only what you will use.

And the last add ToList to return result in order to fetch data.

So here is your new query:

     var availableIMEISearch = from i in dataContext.Items
                          join h in dataContext.Handphones
                          on i.I_ID equals h.HP_ID
                          where(h.IMEI.StartsWith(searchTerm) && (i.I_Status=="Available")
                          select h;
return availableIMEISearch.ToList();

If that still doesn't help.

Verify that you have the data in both tables with join condition;
Open SSMS, run the SQL Server Profiler and chech the query generated. Try to run that query manually to find out whether the data is fetched.

Unable to retrieve record from join table & conditional where clause

1 Answers1