none
Creating an entity connection from a SQL Connection

    Question

  • Background: for security reasons I need to be able to create an entity connection from a SQL connection. I don't have the user password and I cannot use integrated security.

     

    I thought I could use the following code:

     

    MetadataWorkspace wksp = new MetadataWorkspace();

    wksp.LoadFromAssembly(System.Reflection.Assembly.GetExecutingAssembly());

    EntityConnection eConx = new EntityConnection(wksp, sqlConx);

     

    The call to LoadFromAssembly throws an exception "The space 'OSpace' has no associated collection."

     

    I have made sure that the all 3 resources (csdl, msl and ssdl) are in the assembly. I'm not sure where to go from here. Can anybody help?

    Wednesday, July 30, 2008 7:28 PM

Answers

  • Sorry Patrice, I accidently worked on your error instead of answering your question.  here is a sample of how to create an EntityConnection and then an ObjectContext from a SqlConnection rather than just a connection string.  I hope this helps.

     

    Code Snippet

    class Program

    {

    const string connectionString = @"Data Source=sample\sqlexpress;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True";

    static void Main(string[] args)

    {

    MetadataWorkspace workspace = new MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { Assembly.GetExecutingAssembly() });

    // check and be sure the metadata loaded

    Debug.Assert(workspace.GetItemCollection DataSpace.CSpace).GetItems<EntityType>().Count != 0);

    Debug.Assert(workspace.GetItemCollection(DataSpace.SSpace).GetItems<EntityType>().Count != 0);

     

    using (SqlConnection sqlConnection = new SqlConnection(connectionString))

    using (EntityConnection entityConnection = new EntityConnection(workspace, sqlConnection))

    using (NorthwindEntities context = new NorthwindEntities(entityConnection))

    {

    foreach (var product in context.Products)

    {

    Console.WriteLine(product.ProductName);

    }

    }

    }

    }

     

     

    Wednesday, August 6, 2008 10:12 PM
    Moderator

All replies

  • If you create a new ObjectItemCollection, and "Register" it with the MetadataWorkspace before you call LoadFromAssembly it should solve the problem.

     

    jeff

     

    Wednesday, July 30, 2008 10:25 PM
    Moderator
  • I am sorry, Jeff, but I need a little more guidance. As I run from error to error I keep adding lines of code such as:

     

    wksp.RegisterItemCollection(new EdmItemCollection());

    wksp.RegisterItemCollection(new StoreItemCollection());

    wksp.RegisterItemCollection(new ObjectItemCollection());

     

    but I have no idea what I am doing, it's like voodoo incantations!!!

     

    Can you point me to some documentation that explains how this all works? I may be naive but it seems that creating an Entity Connection from an SQL connection should be straightforward. (It certainly was in LINQ to SQL).

     

    Thanks for your help. 

     

     

    Thursday, July 31, 2008 5:50 AM
  • Sorry Patrice, I accidently worked on your error instead of answering your question.  here is a sample of how to create an EntityConnection and then an ObjectContext from a SqlConnection rather than just a connection string.  I hope this helps.

     

    Code Snippet

    class Program

    {

    const string connectionString = @"Data Source=sample\sqlexpress;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True";

    static void Main(string[] args)

    {

    MetadataWorkspace workspace = new MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { Assembly.GetExecutingAssembly() });

    // check and be sure the metadata loaded

    Debug.Assert(workspace.GetItemCollection DataSpace.CSpace).GetItems<EntityType>().Count != 0);

    Debug.Assert(workspace.GetItemCollection(DataSpace.SSpace).GetItems<EntityType>().Count != 0);

     

    using (SqlConnection sqlConnection = new SqlConnection(connectionString))

    using (EntityConnection entityConnection = new EntityConnection(workspace, sqlConnection))

    using (NorthwindEntities context = new NorthwindEntities(entityConnection))

    {

    foreach (var product in context.Products)

    {

    Console.WriteLine(product.ProductName);

    }

    }

    }

    }

     

     

    Wednesday, August 6, 2008 10:12 PM
    Moderator
  • THANK YOU! That works perfectly. It's a lot more straightforward than the previous posts and my own research led me to believe. I'm not sure I understand why the connection has to be closed to start with, but I can live wiht that. May I suggest adding this sample code to the documentation, or even better, adding an EntityConnection constructor that takes a DB connection as a parameter and does just that?

     

    Patrice

     

    Thursday, August 7, 2008 12:34 AM
  • PatriceBG,

    I am trying to use an existing database connection as well.  Could you post a snippet of your code you got working?

    Thanks,

    ezcoder

    Friday, February 6, 2009 6:20 AM
  • If the entity container lives in a different assembly, then you will get the exception "MetadataWorkspace must have EdmItemCollection pre-registered." To fix this, use typeof(the entity container class).Assembly instead of Assembly.GetExecutingAssembly(). If you have multiple entity containers in the same assembly, just pick one of the containers; it doesn't matter which. You can still use the same entity connection for the other containers.
    Sunday, August 9, 2009 7:36 AM
  • This topic and Joe's last post helped me a lot. Thanks all.
    Thursday, November 12, 2009 7:06 PM