Connect to SQL DB via Custom Schema? (code generated from template files - VS 2010)

tkates1

I've recently discovered LINQPad and LOVE it. Up to this point, the connection I've been using is a direct connection to our SQL Server DB. That works great; however, because we use a custom schema, I can't just paste a query from Visual Studio directly into LINQPad and have it work.

Hopefully, what I'm describing below makes sense. If not, please let me know what needs clarification.

In Visual Studio, we use a custom schema, code generator with templates, and IQToolkit (I didn't set this up so I, admittedly, have limited knowledge of how it all works, but if more information is needed regarding this setup, I can ask around here at work...I just need to know what questions to ask.)

For example, here's a (very simple) query I'd write in VS...

from x in HrHrcode
select x.LongDesc

In order to get it to work in LINQPad, I have to change it like so...

from x in Hr_hrcode
select x.Long_desc

...because, as mentioned, I'm connecting directly to the DB in LINQPad and Hr_hrcode and Long_desc are the actual table and column name in the DB (notice the underscores and differences in case...this particular DB is case sensitive.)

I know LINQPad has the option of using typed data contexts from an assembly when creating a new connection, but I can't seem to get it to work because 1) I'm not sure which LINQPad Driver option I should be using (LINQ to SQL, Entity Framework, or Entity Framework DbContext POCO (4.1/4.2/4.3)) and, 2) when choosing one of those options, I'm not sure which assembly I should be using (I've tried all Driver 3 options with various DLLs from our project that I thought would contain the custom entity info, but I guess I haven't hit upon the right combination of Driver option and DLL.)

Is it possible to have LINQPad recognize our custom schema with this setup?

JoeAlbahari

The driver that you need is the IQ driver. Unfortunately, LINQPad doesn't offer a static version of the driver for the IQ toolkit (one that lets you use your own datacontext). Relatively few people use the IQ toolkit in their own projects and those that do, probably use their own versions. So writing a static driver wouldn't really be worthwhile.

The workaround is to add a reference to your assemblies in the query properties dialog instead of adding a connection. Then set the query language to C# statements and do this:

var db = new MyTypedDataContext();

var query = from x in db.HrHrcode
select x.LongDesc;

query.Dump();

Once you've added the references and namespaces, you can save these to a snippet if you have LINQPad Premium edition. Press 'Save as Snippet', and type something like iq. Then to add those references/namespaces to another query, simply type iq TAB.

To avoid LINQPad walking association properties, use Results to Grid mode, or call Dump with a number:

query.Dump(2);

or

query.Dump(true) // Force dump to grids.

tkates1

Thanks, Joe! Glad to know there's a workaround. Now let's see if I can get it to work.

I tried what you recommended...

var db = new ApplicantManagementContext();

var query = from x in db.HRHrcode
select x.LongDesc;

query.Dump();

And it's giving me a

NullReferenceException: Object reference not set to an instance of an object.

error on the "var" keyword on this line...

var query = from x in db.HRHrcode

So I tried this...

var db = new ApplicantManagementContext();
db.Dump();

And it lists all of the table names in the schema, but next to each one of them it says

Object reference not set to an instance of an object.

If I expand the NullReferenceException next to one of the tables, I get the output and stack trace below.

I painstakingly went through and added all of the references from our project (85 of them) and the namespaces I thought I needed, but I get the same results.

I know that without you having specific knowledge about our project you may not be able to help me any further, but do you have any idea what I might still be missing here?

Output and Stack Trace fromNullReferenceException next to one of the tables...

Object reference not set to an instance of an object. 
Message Object reference not set to an instance of an object. 
Data IDictionary (0 items) 
 
InnerException null  
TargetSite 6RuntimeMethodInfo 4  
ApplicantManagementContext.get_AMStaticCodes () 
Name get_AMStaticCodes 
DeclaringType typeof (ApplicantManagementContext)  
ReflectedType typeof (ApplicantManagementContext)  
MemberType Method 
MetadataToken 100686210 
Module 6RuntimeModule 4  
Entities.dll 
MDStreamVersion 131072 
FullyQualifiedName C:\Users\terry.kates\AppData\Local\Temp\LINQPad\lcjtdppw\shadow_ibcwux\entities.dll 
ModuleVersionId 52f2d501-e119-4c69-ac6f-930fd7d26147 
MetadataToken 1 
ScopeName Entities.dll 
Name entities.dll 
Assembly 6RuntimeAssembly 4  
Entities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null 
CodeBase file:///C:/Users/terry.kates/AppData/Local/Temp/LINQPad/lcjtdppw/shadow_ibcwux/entities.dll 
FullName Entities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null 
EntryPoint null  
Evidence 4Evidence 4  
 
PermissionSet 4PermissionSet 4  
 
SecurityRuleSet Level2 
ManifestModule qRuntimeModule 4  
Entities.dll 
 
ReflectionOnly False 
Location C:\Users\terry.kates\AppData\Local\Temp\LINQPad\lcjtdppw\shadow_ibcwux\entities.dll 
ImageRuntimeVersion v4.0.30319 
GlobalAssemblyCache False 
HostContext 0 
IsDynamic False 
EscapedCodeBase file:///C:/Users/terry.kates/AppData/Local/Temp/LINQPad/lcjtdppw/shadow_ibcwux/entities.dll 
IsFullyTrusted True 
 
ModuleHandle 5ModuleHandle 4  
System.ModuleHandle 
MDStreamVersion 131072 
 
 
IsSecurityCritical True 
IsSecuritySafeCritical False 
IsSecurityTransparent False 
MethodHandle 6RuntimeMethodHandle 4  
System.RuntimeMethodHandle 
Value IntPtr 
103405092 
 
 
Attributes PrivateScope, Public, HideBySig, SpecialName 
CallingConvention Standard, HasThis 
ReturnType typeof (IEntityTable<AMStaticCodes>)  
ReturnTypeCustomAttributes 6RuntimeParameterInfo 4  
IQToolkit.IEntityTable`1[DataServices.Entities.ApplicantManagement.AMStaticCodes]  
ParameterType typeof (IEntityTable<AMStaticCodes>)  
Name null  
DefaultValue null  
RawDefaultValue null  
MetadataToken 134217728 
Position -1 
Attributes None 
Member qRuntimeMethodInfo 4  
ApplicantManagementContext.get_AMStaticCodes () 
 
IsIn False 
IsOut False 
IsLcid False 
IsRetval False 
IsOptional False 
 
ReturnParameter 6RuntimeParameterInfo 4  
IQToolkit.IEntityTable`1[DataServices.Entities.ApplicantManagement.AMStaticCodes]  
ParameterType typeof (IEntityTable<AMStaticCodes>)  
Name null  
DefaultValue null  
RawDefaultValue null  
MetadataToken 134217728 
Position -1 
Attributes None 
Member qRuntimeMethodInfo 4  
ApplicantManagementContext.get_AMStaticCodes () 
 
IsIn False 
IsOut False 
IsLcid False 
IsRetval False 
IsOptional False 
 
IsGenericMethod False 
IsGenericMethodDefinition False 
ContainsGenericParameters False 
IsPublic True 
IsPrivate False 
IsFamily False 
IsAssembly False 
IsFamilyAndAssembly False 
IsFamilyOrAssembly False 
IsStatic False 
IsFinal False 
IsVirtual False 
IsHideBySig True 
IsAbstract False 
IsSpecialName True 
IsConstructor False 
 
StackTrace    at DataServices.Entities.ApplicantManagement.ApplicantManagementContext.get_AMStaticCodes() 
HelpLink null  
Source Entities