locked
MVC3 and Database Schema question RRS feed

  • Question

  • User-1312353009 posted

    I'm using a basic model (class file) based on an existing database table, NOT EF, but the tables in the database are not in the default "dbo" schema.  I'd like to know what, if any, are the options to tell my model/controllers to use a different schema?  Each table has it's own class in the models folder.

    This is similar to what is used in CodeFirst.  I have also created another class with the DbSet<> properties inheriting from DbContext.

    I can't seem to find anything on the web...or here on this that doesn't involved the old .cdml files from an EF generated file.  I'm not using EF at all, as I mentioned.

    Thursday, August 11, 2011 5:31 PM

Answers

  • User-474980206 posted

    as we learned in database 10, if the schema name is not specified, then the database looks for a table with a schema to match the logged in users schema, if not found, the use dbo.

    if you do not specify the schema name in the EF mapping, then how is it supposed to guess. please read the documentation. its clear you haven't.

    namespace is trival:

    namespace MyApp.Database
    {
          public MyAppContext : DbContext
           {
                 public DBSet<Schema1.authors> authors {get; set;}
    
                 protected override void OnModelCreating(DbModelBuilder modelBuilder) 
                 { 
                          base.OnModelCreating(modelBuilder); 
                          modelBuilder.Entity<Schema1.authors>().ToTable("Schema1.authors");
    modelBuilder.Entity<Schema2.authors>().ToTable("Schema2.authors"); } } public class Schema1 { public class authors { } } public class Schema2 { public class authors { } } }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 12, 2011 1:20 PM

All replies

  • User197322208 posted

    I'd like to know what, if any, are the options to tell my model/controllers to use a different schema? 

    The model/controller does nothing to have with the schema of database.

    Your DAL code,

      I have also created another class with the DbSet<> properties inheriting from DbContext.
    ,yes.

    You can assign the schema to an user and use in the connection string the user.

    Thursday, August 11, 2011 6:31 PM
  • User-1312353009 posted

    "Your DAL code"

    I don't know what you mean by this in the context of an MVC3 application.  I'm looking into the possibility of converting from "N-Layers" and web controls to MVC3, so beyond simple tutorials that make ID and schema assumptions, I have no clue what it is you're talking about.  I know what a DAL is, just not how it's used in MVC.

    Friday, August 12, 2011 9:55 AM
  • User197322208 posted

    "Your DAL code"

    I don't know what you mean by this in the context of an MVC3 application.

    For me the DBSet<T> is DAL code.( data access layer)

    I know what a DAL is, just not how it's used in MVC.

    Did you folllow tutorials from http://www.asp.net/mvc/?

    More, I had give a solution. Create a user, assign the schema, use the user in the connection string.

    Friday, August 12, 2011 10:07 AM
  • User-1312353009 posted

    The Music Store one, yes.  But I'm an Enterprise developer, not a "shade tree" developer who uses "dbo" schemas in the database, assigns users and passwords in connection strings, and who likes to keep the data layer seperated from the presentation layer via a business layer.  So, using one of our existing databases, which doesn't use tables in the dbo schema, i created the model classes.  I was able to use the "[Key]" attribute on our primary keys (because we don't name our PK's in a way MVC can make an assumption), and was just curious if there was a way to explicitly define the schema, like I did the primary key, so MVC would find the tables in the database, and eventually load data.

    So I disagree, no solution has been provided in the context of "your DAL code" and "yes".  Can you provide an example?

    Friday, August 12, 2011 10:14 AM
  • User-474980206 posted

    Your comments makes no sense. MVC has no database code, and does no know how to talk to tables. MVC has view models used for binding  data (messages) between viws and controllers. 

    all database work must be done with some other library. EF, linq to sql are comon, but any framework can be used.  MVC was designd assumimg you would have a seperate BI layer that handled database work. If your BI layer uses models, then the MVC binder may work directly on these models (like the examples), but in an enterprise app, the view models would be seperate from the BI models.

    how you represent you schema name in you BI models is up to you, MVC doesn't care becuase it doesn't know about them, or keys for that matter. It just knows POCO objects, List<> and arrays.

    note: if using linq to sql, or ef, then the schema name is in the mapping layer (source property).

    Friday, August 12, 2011 10:29 AM
  • User197322208 posted

    But I'm an Enterprise developer, not a "shade tree" developer who uses "dbo" schemas in the database, assigns users and passwords in connection strings, and who likes to keep the data layer seperated from the presentation layer via a business layer.

    Very well.

    I suppose that, as an Enterprise developer :

    1. you are familiar with database security / schema objects.

    2. you know that , at setup time, you configure the database. Just create a schema, an user for this schema and put in the application connection string.

    Could you make that user in the database and assign the schema?

    Friday, August 12, 2011 10:32 AM
  • User-1312353009 posted

    ok.  as in the original post, I mentioned that my code is similar to "CodeFirst", i.e. models and the class file which inherits from DataContext with the "Entities" (DbSet<T> properties).  And of course, the connection string in the web config.  I'm very new to MVC and how it works.  And, I'm trying to avoid having a project with an EF model in it for these reasons:

    • Requires naming primary keys "BlahID or ID" which we don't do
    • Cannot update the code gen code otherwise to attribute the "[Key]" or it will eventually get overwritten
    • And I don't like the idea of connections strings in a .config file (which I'm still doing with MVC, and don't like)
    • It always seems to find a way to break itself when you have to do an update
    • 5,000+ line class file of code generated ... code that I can't manipulate or manage

    You're right, MVC has no database code.  So, in the example of CodeFirst, you create a class file (object) and an entity of that object (DbContext) and a connection string, and it magically has all the CRUD features...but no database code.

    So by that logic, why can't I create a class object, an DbContext Entity, a connection string, and point it to an existing database/table?  I can, and it WOULD work just fine, IF our tables were in the dbo schema, but they are not.

    How do I tell what I have (the MVC app with the models based on an existing database) to look at another db schema?

    Friday, August 12, 2011 10:58 AM
  • User-1312353009 posted

    I'm a C# developer, not a SQL developer.  I don't set up the databases here, and the user security is based on SQL Roles and Active Directory Groups in our organization.  We don't create SQL users on the SQL server and assign them to database, etc..  In fact, that hasn't been the norm in any organization I've worked for since SQL 7, and then it was converted from a mess of SQL users and unmanageable BS to AD once the DBA was fired.  Any organization that follows a business practice of SQL users on databases needs to fire their DBA and find someone with the knowledge and foresight of how to scale security in db roles and filter the access through the domain admins (i.e., adding users to a user group in AD).

    But we're not here to discuss SQL security.  Nor do I have the ability to change the database security nor the inclination to ask the DBA to change/alter or add logins so I can "play" with MVC, which is all I'm doing.  All I've learned so far is posting a question on ASP.NET is a nightmare (can you tell the frustration level is increasing?).

    Do you have a specific tutorial on ASP.NET/MVC in mind that might answer my question Andrei beyond a bogus Music Store or a Julie Lorman BLOG app????  Because so far, this has been a waste of my time...no offense, but really, if you're not going to truly answer a presumably simple question with a detailed answer, then please, don't reply.  I know you're an MVP and moderator, and because of that, I inherently expect more from you.

    Friday, August 12, 2011 11:10 AM
  • User-474980206 posted

    you are still confusing MVC with the BI layer. DbContext is either linq to sql or entitiy framework, and is not part of MVC. you can use any database logic you want.

    DBSet is the entity framework, which you claim to not want to use.

    also with either linq to sql or entity frame you can can just point to your tables. your only issue is if the same table name is in two schemas, you will need to create a unique class name for one of them (or both). there is no requirement that the model names match the table names. also with code first you could namespace the schemas if you want. 

    also I have no idea why you keep bringing up the dbo schema, as schema names are fully supported by inq to sql and EF.

     

    Friday, August 12, 2011 11:44 AM
  • User-1312353009 posted

    Ok.  I'm working in an MVC application, so that's why I keep saying MVC.  What I don't want to use from EF is the Modeling that generates a 5,000 line uncostumizable nightmare of a class (I thought I mentioned that).  I know I'm using the EF in the aspect of DbContext, I don't have a problem with that.  I want full control over my MODELS (MVC) that I can attribute and modify as necessary, not a 5,000 line generated juggernaut.

    "your only issue is if the same table name is in two schemas"  I don't undestand that line at all.  My issue is I have database "DBX" with table "TABLEX" in schema "X", not dbo.  Whatever framework/db connectivity is being used in my MVC application, is looking at DBX for TABLEX in schema DBO, that's why I keep bringing it up.

    "also with code first you could namespace the schemas if you want"  How?

    "also with either linq to sql or entity frame you can can just point to your tables."  How, if the application keeps looking for the table in the wrong db schema?

    Error rec'd (inner exception):  {"Invalid object name 'dbo.TABLEX'."}  To me, this means it's looking at the wrong schema, right?

    Friday, August 12, 2011 12:07 PM
  • User-474980206 posted

    as we learned in database 10, if the schema name is not specified, then the database looks for a table with a schema to match the logged in users schema, if not found, the use dbo.

    if you do not specify the schema name in the EF mapping, then how is it supposed to guess. please read the documentation. its clear you haven't.

    namespace is trival:

    namespace MyApp.Database
    {
          public MyAppContext : DbContext
           {
                 public DBSet<Schema1.authors> authors {get; set;}
    
                 protected override void OnModelCreating(DbModelBuilder modelBuilder) 
                 { 
                          base.OnModelCreating(modelBuilder); 
                          modelBuilder.Entity<Schema1.authors>().ToTable("Schema1.authors");
    modelBuilder.Entity<Schema2.authors>().ToTable("Schema2.authors"); } } public class Schema1 { public class authors { } } public class Schema2 { public class authors { } } }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 12, 2011 1:20 PM
  • User-1312353009 posted

    "please read the documentation. its clear you haven't."  no need to be snarky.  i started with a tutorial, like everyone else does.  Every tutorial says "for simplicity sake, we'll just use the CodeFirst blah blah blah" and not a single one, include PluralSight, EVER mentioned this.

    If there is documentation, I certainly haven't been able to locate it here on ASP.NET/MVC, all I see are tutorials, some on EF.  Or do you mean MSDN, click here, to click here, to click here, to see no example, to click back for a generic or no explanation at all example that is utterly useless to click over here and hop eyou find what you're looking for documentation?

    See? I can be snarky too.  How about a link?  Or a code snippet, like you just offered, which answered my bloody question...

    At any rate...  THIS was the answer:

                 protected override void OnModelCreating(DbModelBuilder modelBuilder)
                 {
                          base.OnModelCreating(modelBuilder);
                          modelBuilder.Entity<Schema1.authors>().ToTable("Schema1.authors");
                          modelBuilder.Entity<Schema2.authors>().ToTable("Schema2.authors");
                  }

    Thank you!

    Now, what documentation are you talking about?  My only accomplishments in MVC3 are three tutorials, one was on EF, all we're codefirst or modelfirst on non-enterprise level basic stuff (you know, for easy examples of how not to do things in the real world), all over the course, off and on, for the last two weeks.  That is my total exposure.  So, if I haven't read the documentation, that is why.  I learn by doing and asking questions, not reading lines of code in crappy examples using Console.WriteLine.

    Cheers!  Thanks again!

    Friday, August 12, 2011 2:25 PM
  • User197322208 posted

    I'm a C# developer, not a SQL developer

    If you work with database code, please be also a SQL developer and study schema / indexes / primary keys / and so on.

    and the user security is based on SQL Roles and Active Directory Groups in our organization. 

    If the connection string  on Sql Server is based on each AD user identity, then it is clear that you do not reuse connections. So it is , for me, a clear way to have more and more connections to sql server, depending on your organization size.

    Any organization that follows a business practice of SQL users on databases needs to fire their DBA and find someone with the knowledge and foresight of how to scale security in db roles and filter the access through the domain admins (i.e., adding users to a user group in AD).

    Respectely disagree.

    Anyway, the solution of hard coding in the application the schema instead of having allocated to the user is for me the clear idea that you (Later edit : your organization ) fired the DBA and now you do not know who to call for a simple task ...

    Friday, August 12, 2011 5:29 PM
  • User-1312353009 posted

    The question has been answered, but thanks for your reply.  I've done my fair share of database development, and was the primary DBA for a number of years covering SQL7-SQL 2005.  I also have worked heavily with 2008 and 2008 R2 and am involved in the database development for all applications.  I know what I'm talking about.  However, I'm the lead developer here, which means I'm NOT the DBA and have no control over the security and all the other items i explained.  our security here is effective and works flawlessly.  I'm not going to try to manage 15-30 connection strings in a web.config file per user per database or some crap like that, it's all integrated security, meaning one connection string (which we don't even use in our applications because, well face it, it's an XML file that's not encrypted so any access to it by an outside source can pull all your users and passwords from it).  You use none of that.

    But you can assume what you want.  I replaced the DBA that was fired (years ago, at a completely different organization, in a completely different town).  I had no hand in the firing.  So your assumption is wrong.

    Friday, August 12, 2011 5:48 PM
  • User-981899747 posted

    You can also decorate the class definition with the TableAttribute which accepts a schema parameter. Something like this:

    [Table(Name="MyTable",Schema="MySchema")]
    public class MyClass

    Or like this:

    [Table(Name="MySchema.MyTable")]
    public class MyClass

    Here's a thread on how to programatically change the schema using the fluent syntax Bruce showed you:

    http://social.msdn.microsoft.com/Forums/pl-PL/adodotnetentityframework/thread/e276fb02-ef9b-44eb-b481-699c1c25602d

    And here's an article written by Julie Lerman on Code First Data Annotations:

    http://msdn.microsoft.com/en-us/data/gg193958

    P.S. Julie Lerman literally wrote the book on Entity Framework, so I wouldn't be so quick to dismiss her tutorials. I also think you owe Bruce and Andrei an apology.

    EDIT:

    Here's the documentation for the TableAttribute:

    http://msdn.microsoft.com/en-us/library/system.componentmodel.dataannotations.tableattribute%28v=vs.103%29.aspx

    Friday, August 12, 2011 8:45 PM
  • User-1312353009 posted

    Oh, not dismissing the tutorials.  I've met Julie, she's a pretty cool character.  It's any of the intro tutorials that say "you wouldn't do this in the real world, but for simplicity...blah blah blah".  One of her tutorials was a great intro into MVC3 and EF4.1.  I haven't chased after any of the others.

    And:  {"Invalid object name 'dbo.schema.Table."}...Your claass decoration didn't work.  :}  That's ok though, I have my solution.

    Thanks!

    Monday, August 15, 2011 9:35 AM
  • User-981899747 posted

    Oh, not dismissing the tutorials.  I've met Julie, she's a pretty cool character.  It's any of the intro tutorials that say "you wouldn't do this in the real world, but for simplicity...blah blah blah".  One of her tutorials was a great intro into MVC3 and EF4.1.  I haven't chased after any of the others.

    And:  {"Invalid object name 'dbo.schema.Table."}...Your claass decoration didn't work.  :}  That's ok though, I have my solution.

    Thanks!

    Ok, try this. It worked for me.

    namespace CodeFirstSchemaDemo.Models
    {
        [Table("Authors",Schema="Schema1")]
        public class Author
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    
        public class MyContext : DbContext
        {
            public DbSet<Author> Authors { get; set; }
        }
    }
    Monday, August 15, 2011 10:51 AM
  • User101082546 posted

    I came to this thread whilst searching for info on DbSet, and I have to say I'm very unimpressed with the way a Moderator has handled this thread. Please remember that inexperienced programmers (like me) seek serious information in these forums, especially when an important piece of information is absent from a tutorial. We are not here to get dragged into petty squabbles. If you don't understand a query, then please ask for clarification rather than rant. Thank you.

    Monday, December 26, 2011 5:50 PM
  • User749513692 posted

    MVC Has nothing to do with your database modle

    MVC is really confusing at first but after few months you will master it, just stay focused

    Regards

    CouthieINC

    Wednesday, July 10, 2013 8:20 AM