1

I have some LINQ to SQL classes that are single tables in the database (ie, I dragged and dropped the table from the SQL Server Object Browser to the DBML design layout window). I am considering filtering the table when I fill my view model observable collection like so: (commented out, but you get the idea):

class ObservableDocuments : ViewableCollection<Document>
{
    public ObservableDocuments(DataClasses1DataContext dataDc)
    {
        foreach (Document doc in dataDc.Documents)//.Where(x => x.JobID == 1))
        {
            this.Add(doc);
        }
    }
}

I am wondering if this is an optimal way to handle this filtering. Does LINQ to SQL wait until this call before actually querying the database, or is the entire table still pulled in to the DataContext.Documents table, and then I am only adding the ones that I want to the ObservableCollection? If it is the latter, then is there a better way in LINQ to SQL to filter results, or should I move away from this framework?

Paul Gibson
  • 622
  • 1
  • 9
  • 23
  • Is //.Where(x => x.JobID == 1)) what trying to filter because you commented it out – COLD TOLD Apr 06 '15 at 21:15
  • @COLDTOLD Yes, showing how I currently do it (no filter), and what I am planning to do (filter) but I am in the process of adding the JobID fields and some other logic to suppport it. The question struck me so I want to ask before I get too far in case there is something I overlooked. – Paul Gibson Apr 06 '15 at 21:20
  • Do you care if documents are added to the database after your class has been constructed? What if you've cached some documents that have since been deleted? It might be better to just take a connection string in your constructor, and then populate documents on demand. – Rufus L Apr 06 '15 at 21:28
  • @RufusL, I was thinking I might do this, but that seems to be sidestepping the benefits (CRUD) of using the LINQ to SQL classes. IE, the delete, update, and insert operations are then each requiring more code as well. – Paul Gibson Apr 06 '15 at 21:55

1 Answers1

2

Using LINQ to SQL means that applying LINQ operators on an IQueryable<T> will be added to the SQL statement instead of using IEnumerable<T> which applies the operator on the fully retreived result.

So when applying an Where operator, in your case, and using the combination IQueryable<T> will be good enough. The results will be populated if you iterate over the results or call ToList().

More information: Using IQueryable with Linq

Community
  • 1
  • 1
Martijn van Put
  • 3,293
  • 18
  • 17
  • I looked at the link you provide, and I think I understand. BUT . . . this is a black box in VS . . . I drag and drop tables and the DataContext class code is automatically generated. So how do I ensure that I am using an IQueryable? And will I still be able to call the SubmitChanges() throughout for updates and inserts/deletes? From what I see it appears that the Where method is an IQueryable . . . but I can't really be sure. – Paul Gibson Apr 06 '15 at 21:40
  • By default DbContext will use IQueryable. You can verify this by hovering the mouse over the Documents and Where to check the type and return type. – Martijn van Put Apr 06 '15 at 21:46