Home

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

edited October 2012
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?

Comments

  • edited October 2012
    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.
  • 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 
    
Sign In or Register to comment.