LINQ - NOT selecting certain fields?

Question

I have a LINQ query mapped with the Entity Framework that looks something like this:

image = this.Context.ImageSet
                    .Where(n => n.ImageId == imageId)
                    .Where(n => n.Albums.IsPublic == true)
                    .Single();

This returns a single image object and works as intended.

However, this query returns all the properties of my Image table in the DB. Under normal circumstances, this would be fine but these images contain a lot of binary data that takes a very long time to return.

Basically, in it current state my linq query is doing:

Select ImageId, Name, Data
From Images
...

But I need a query that does this instread:

Select ImageId, Name
From Images
...

Notice i want to load everything except the Data. (I can get this data on a second async pass)

Answer 1

This will create a new image with only those fields set. When you go back to get the Data for the images you select, I'd suggest going ahead and getting the full dataset instead of trying to merge it with the existing id/name data. The id/name fields are presumably small relative to the data and the code will be much simpler than trying to do the merge. Also, it may not be necessary to actually construct an Image object, using an anonymous type might suit your purposes just as well.

image = this.Context.ImageSet
                    .Where(n => n.ImageId == imageId)
                    .Where(n => n.Albums.IsPublic == true)
                    .Select( n => new Image { ImageId = n.ImageId, Name = n.Name }
                    .Single();

Answer 2

Alternatively you could use the select new in the query expression...

var image =
(
    from i in db.ImageSet
    where i.ImageId == imageId && i.Albums.IsPublic
    select new
    {
        ImageId = i.ImageId,
        Name = i.Name
    }
).Single()

The LINQ query expressions actually get converted to the Lambda expression at compile time, but I prefair using the query expression generally because i find it more readable and understandable.

Thanks :)

Answer 3

[If using Linq 2 SQL] Within the DBML designer, there is an option to make individual table columns delay-loaded. Set this to true for your large binary field. Then, that data is not loaded until it is actually used.

[Question for you all: Does anyone know if the entity frameworks support delayed loaded varbinary/varchar's in MSVS 2010? ]

Solution #2 (for entity framework or linq 2 sql):

Create a view of the table that includes only the primary key and the varchar(max)/varbinary(max). Map that into EF.

Within your Entity Framework designer, delete the varbinary(max)/varchar(max) property from the table definition (leaving it defined only in the view). This should exclude the field from read/write operations to that table, though you might verify that with the logger.

Generally you'll access the data through the table that excludes the data blob. When you need the blob, you load a row from the view. I'm not sure if you'll be able to write to the view, I'm not sure how you would do writes. You may be able to write to the view, or you may need to write a stored procedure, or you can bust out a DBML file for the one table.

Answer 4

hey vidalsasoon

Unfortunately, if using LINQ to SQL, there is no optimal solution.

You have 3 options:

1. You return the Entity, with Context tracking and all, in this case Image, with all fields
2. You choose your fields and return an anonymous type
3. You choose your fields and return a strongly typed custom class, but you lose tracking, if thats what you want.

I love LINQ to SQL, but thats the way it is.

My only solution for you would be to restructure your DataBase, and move all the large Data into a separate table, and link to it from the Image table.

This way when returning Image you'd only return a key in the new DataID field, and then you could access that heavier Data when and if you needed it.

cheers