I'm using Linq To Entities so I can Linq to my MySql database. My problem is that I've got a table with large text fields, so I don't want to load them unless I need them, and the L2E lazy loading model doesn't work because (1) the MySql provider doesn't seem to support lazy loading at all, and (2) even if it did, I don't think it would be correct anyway.
Stripped down to illustrate the problem, I have a large table with columns: Id, Text1, Text2, Text3
I'll have a routine that needs to work only with Text1, and a different routine that works with Text2. Because the contents of the text fields are large, I really don't want to select the text fields I'm not using for a given routine.
In other words, doing this would be too expensive:
Dim items = From i In db.MyTable Where [predicate]
But doing this:
Dim items = From i In db.MyTable Where [predicate] Select i.Id, i.Text1
creates an IQueryable(Of anonymous_type), instead of an IQueryable(Of MyTable), and since I need to be able to update various fields and call SaveChanges, I need MyTable objects to do this. I can't convert the anonymous type to a new MyTable because the DataContext wouldn't know about it and thus updates wouldn't work.
(And it seems to me (though this may be my lack of L2E knowledge), that even if the MySql provider supported lazy loading, that would mean that I could specify lazy loading for the Text fields, but then it would make N queries to the server as I looped through N records. In other words, lazy loading seems to be for when you might but probably won't need a big data column, not for when you know you want that column, but you don't want the other columns.)
So the question is: how can I select only the fields I want while still getting an object of the right type so that I can update it later?