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?
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
{
}
}
}
"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...
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.
If you work with database code, please be also a SQL developer and study schema / indexes / primary keys / and so on.
RagMan85
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.
RagMan85
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 ...
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.
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.
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.
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; }
}
}
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.
RagMan85
Member
43 Points
18 Posts
Re: MVC3 and Database Schema question
Aug 12, 2011 04:07 PM|LINK
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?
bruce (sqlwo...
All-Star
28738 Points
4249 Posts
Re: MVC3 and Database Schema question
Aug 12, 2011 05:20 PM|LINK
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 { } } }RagMan85
Member
43 Points
18 Posts
Re: MVC3 and Database Schema question
Aug 12, 2011 06:25 PM|LINK
"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!
ignatandrei
All-Star
107523 Points
16385 Posts
Moderator
MVP
Re: MVC3 and Database Schema question
Aug 12, 2011 09:29 PM|LINK
If you work with database code, please be also a SQL developer and study schema / indexes / primary keys / and so on.
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.
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 ...
RagMan85
Member
43 Points
18 Posts
Re: MVC3 and Database Schema question
Aug 12, 2011 09:48 PM|LINK
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.
chohmann
Star
9375 Points
1644 Posts
Re: MVC3 and Database Schema question
Aug 13, 2011 12:45 AM|LINK
You can also decorate the class definition with the TableAttribute which accepts a schema parameter. Something like this:
Or like this:
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
RagMan85
Member
43 Points
18 Posts
Re: MVC3 and Database Schema question
Aug 15, 2011 01:35 PM|LINK
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!
chohmann
Star
9375 Points
1644 Posts
Re: MVC3 and Database Schema question
Aug 15, 2011 02:51 PM|LINK
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; } } }aledad
Member
49 Points
35 Posts
Re: MVC3 and Database Schema question
Dec 26, 2011 09:50 PM|LINK
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.