visionary (3) [Avatar] Offline
#1
Hi Guys,

I've been looking around for a solution to this, but as yet have been unsuccessful in my search..

Given the following SQL:

"select object_name([object_id]) from sys.columns"

What would a pure LINQ equivalent be?

Stuff i've read so far has mostly ended up with one of two solutions:

1. Embed pure SQL:
var columnNames = ExecuteQuery<string>("select object_name([object_id]) from sys.columns")

or...
2. Use reflection, for which I don't have any examples.


I would have expected to be able to use something along the lines of:

var sysColumns =
from cols in sys.Columns
select cols.Object_id;


and then somehow calculate object_name from the id... :S

the reported error from the above is: "The name 'sys' does not exist in the current context"
even though I have the master database selected in LINQPad.

Any help greatly appreciated

regards
LINQNewbie
fabrice.marguerie (224) [Avatar] Offline
#2
Re: Accessing sys objects in pure LINQ
If I open a master database in LinqPad, I get the expected result if I use the following C# expression:

from col in Columns
select col.Object_id


Note the missing "sys.".

Fabrice
visionary (3) [Avatar] Offline
#3
Re: Accessing sys objects in pure LINQ
Hmm, interesting.

Ok, so how about using the system tables from the regular database. I notice that none of the following work:

var sysColumns =
from cols in syscolumns join obj in sysobjects on cols.id equals obj.id
select obj.Name;


var sysColumns =
from cols in sys.columns join obj in sys.objects on cols.object_id equals obj.object_id
select obj.Name;


var sysColumns =
from cols in Columns
select cols.id;
fabrice.marguerie (224) [Avatar] Offline
#4
Re: Accessing sys objects in pure LINQ
I'm not too sure about what LinqPad generates in its automatic DataContexts/ObjectContexts.
You're probably better off creating your own models with LINQ to SQL or Entity Framework. You'll then have precise control on the database objects you want to get access to.

The fact that LinqPad doesn't really handle schemas doesn't help.

Fabrice
fabrice.marguerie (224) [Avatar] Offline
#5
Re: Accessing sys objects in pure LINQ
Here is how to proceed for LINQ to SQL.
visionary (3) [Avatar] Offline
#6
Re: Accessing sys objects in pure LINQ
Hi Fabrice,

Thank you for taking the time to answer me, I will investigate this and let you know how I get on..

Great book btw smilie