The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

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