Take the 2-minute tour ×

I have the script:

    object_name(fk.parent_object_id) 'Parent table',
    c1.name 'Parent column',
    object_name(fk.referenced_object_id) 'Referenced table',
    c2.name 'Referenced column'
    sys.foreign_keys fk
inner join
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
inner join
    sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
inner join
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id

And I know that I get a result set back with 5 columns. Is there is a slick and efficient way to store this data in a linq type object or an iQueryable object? I want to be able to iterate through it...

share|improve this question
There are many ways. Are you asking which ORM is best? That's too open-ended... –  RedFilter Nov 3 '11 at 13:27
are you using EF / Linq2SQL ? or "pure" ADO.NET ? –  Yahia Nov 3 '11 at 13:28
add comment

4 Answers

There are a few ORMs that use IQueryable: Entity Framework, LINQ to SQL, NHibernate, Subsonic, etc. I recommend trying one out.

share|improve this answer
add comment

For a very lightweight ORM, you can use the DataContext's ExecuteQuery:

class YourRow
    public string Col1 { get; set; }
    public string Col2 { get; set; }

// DataContext takes a connection string as parameter
var db = new DataContext("Data Source=myServerAddress;" +
    "Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;");
var rows = db.ExecuteQuery<YourRow>(@"
    select  fk.name,
            object_name(fk.parent_object_id) 'Parent table',

If you can store your SQL query in a view, you can drag the view to a DBML file to have LINQ create the wrapper class for you.

share|improve this answer
add comment

If you execute this script by using a DbCommand and a DataReader you can iterate over the rows that are returned and add them to a list of a custom object that will hold the 5 columns.

You can then use Linq To Objects to filter the list even more.

Or you can use an ORM to map your entities to the database. If you create a Database View for your query you can map an entity to the view with for example the Entity Framework and use that to execute further queries.

share|improve this answer
add comment

You can store this into DataTable. You have add assembly System.Data.Exetensions in your project to used DataTable as IQueryable.

from tbl in dataTable.AsEnumerable()
//where clause
select tbl;

DataView view = tbl.AsDataView();
share|improve this answer
add comment

Your Answer


