none
Custom filtering when using SPs as LINQ Methods RRS feed

  • Question

  • Hello,

     

    In the project I'm working on at the moment, I use stored-procs for any action with DB, selecting, updating etc. To avoid writing of the new procs for filtering results I thought of using LINQ features like .Where().

     

    When using it with table itself, it works perfectly

     

    for example:

     

    SomeDataContext sdc = new SomeDataContext();

    DataGridView.DataSource = sdc.SomeTable.Where(p=>p.Name.Contains("filter"));

     

    it displays everything as required, but when I try to use some method instead of directly accessing table, it doesn't return any results at all:

     

    SomeDataContext sdc = new SomeDataContext();

    DataGridView.DataSource = sdc.SomeTable_GetAll().Where(p=>p.Name.Contains("filter"));

    it doesn't show any rows in datagridview. Sad

     

    I would really appreciate if someone shows me the way how can I filter results returned by stored-proc client-side, many many thanks in advance Smile

    Thursday, July 3, 2008 12:59 PM

Answers

  • The query support in LINQ to SQL is designed to generate and use dynamic queries, when you use stored procedures there is no way for it to pass additional criteria to the stored procedure.

     

    While you could force it to return the results into a List or IEnumerable source and then query that using LINQ to Objects this will incurr a lot of data transfer (all records from the SP) and memory overhead so wouldn't be something I would recommend.

     

    For tables where you want to be able to modify selection criteria using Where dynamic query generation is recommended over stored procedures.

     

    [)amien

    Friday, July 11, 2008 8:24 PM
    Moderator