Linq to SQL - Don't fetch a particular column

Question

Is there a way to not fetch a specific column using linqtosql without having to use an anonymous type and specify each returned filed individually?

We use SQLMetal to generate the dbml file which contains all the types into which to put the queried data results. However, when select columns are included in the linq query, the results go into an anonymous type instead of the type declared in the dbml file. I would like to select all but one of the columns from a particular table, but still have the results returned in the related dbml type.

Any ideas appreciated.

Answer 1

LINQ to SQL supports lazy loading individual properties. In the DBML designer, you can set Delay Loaded to true in a column's properties. Columns that are delay loaded will not be included in the initial SELECT. If you try to access the object's property and it has not been loaded yet, then another SELECT statement will be executed to bring this value from the DB.

If you are editing the DBML file by hand, set <Column IsDelayLoaded="true">. If you are writing your LINQ to SQL classes by hand, it is as simple as declaring the property's backing field as a Link<T> instead of T. For example:

[Table]
public class Person
{
    private Link<string> _name;

    [Column(Storage = "_name")]
    public string Name
    {
        get { return _name.Value; }
        set { _name.Value = value; }
    }
}

See also the "Delay/Lazy Loading" section in this post by Scott Guthrie.

---

Update: The above answer applies if you want the column to still be available when you need it. It will not be included in SELECTs unless you specifically ask for it (see LoadOptions) or try to access it.

If you just don't want to use or access the column at all, and you don't want it available as a class property, then go with Serapth's answer of removing the column from the DBML file. Be sure you understand the implications, such as the loss of concurrency checking on that column.

Answer 2

If you are extremely lazy, why not add the table a second time via DBML designer, rename it MyTableWithOutColumnX then delete the record you don't want returned? Just hilite the column name and hit delete.

Its sloppy, but then, to a certain degree, so is having a table where a record shouldn't appear sometimes. More then anything, it is easy, its all abstracted in the DBML so it shouldnt impact your code, other than switching which table to access. Well named, it might even make sense to someone maintaining your code in the distant future.