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

The way that LINQ to SQL knows what columns to include is by the anonymous type you project over the query. I don't think there is any other way to have LINQ to SQL exclude columns as a straight projection will return everything.

Answer 2

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 3

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.

Answer 4

Thanks all for your input. The final solution I have settled on is to simply specify the columns i want to bring back BUT to do this within the Linq statement in a new object of the type I want... an example should help!:

Table log has three colums

• LogID
• DateLogged
• SerializedData

But I only want DateLogged & Serialized data. However, I would like this within a datacontainer generated by SQLMetal

I achieved this with the following statement:

---

Dim q = From logItem In dc.Log Select New Log With {.LogID = logItem.LogID, .DateLogged = logItem.DateLogged}

---

Hope that helps someone else out there!