0

The query I'm using is this:

int? UserId = db.StudentModel//the question mark here makes the int nullable
                .Where(c => c.UserName == certUserName)
                .Select(c => c.UserId)
                .First();

Unfortunately, this only works if the database has a primary key. Because of the specific structure and use of the database, there can't be a primary key. How do I modify my search to pull the exact same thing from the database even though there isn't a PK?

EDIT: Sorry! I should've been more explicit. I guess the error itself doesn't lie with the actual query, it lies with the way the models are set up:

One or more validation errors were detected during model generation:

IronGriffin.Models.CompletionsModel: : EntityType 'CompletionsModel' has no key defined. Define the key for this EntityType.

Completions: EntityType: EntitySet 'Completions' is based on type 'CompletionsModel' that has no keys defined.

That being said, the issue is still that there's no primary key defined. I imagine that this would all be functional if the search didn't function by searching the primary keys (unless it doesn't? I thought I read somewhere that that's how it works)

Kulahan
  • 512
  • 1
  • 9
  • 23
  • What do you mean by 'only works' - do you mean like this? http://stackoverflow.com/questions/3996782/entity-framework-table-without-primary-key. There are some very good points in this post, mainly: a table without a primary key is a BAD idea. – Nick.Mc Jul 30 '14 at 00:56
  • Hi - can you give us some more info eg what error message do you get when you try the code above? what have you tried instead? what happened when you researched how to query tables without primary keys? Can you tell us why the table needs to have no key etc? anything you give us will help us to help you solve your problem :) – Taryn East Jul 30 '14 at 00:58
  • You're correct that it's a bad idea, but in this case, it's necessary. – Kulahan Jul 30 '14 at 00:59
  • That query will work regardless of whether the table has a primary key. It will get the `UserId` value from the first record in the table with a matching `UserName`. All that matters is that those two columns exist. – jmcilhinney Jul 30 '14 at 01:25
  • If you can't come up with a natural key on your table add a identity column with auto-incrementing int instead. – Yuck Jul 30 '14 at 02:09
  • Would that solve the issue? I'm under the impression that the the search uses the primary key which, if it's a random number, wouldn't work anymore. – Kulahan Jul 30 '14 at 02:30
  • -1 This question should be removed and re-posted as "How can I define and use EF entities without a primary key"? – Keith Payne Jul 30 '14 at 02:59
  • 1
    *If it doesn't have a primary key, it's not a table* - Old SQL saying – marc_s Jul 30 '14 at 04:49

3 Answers3

8

Unfortunately, this only works if the database has a primary key. Because of the specific structure and use of the database, there can't be a primary key.

Two things: first of all, databases don't have primary keys, tables do. I assume you mean that you have a table in your database that doesn't have a primary key defined for it.

Which leads to number two: to use Entity Framework, every entity must have a primary key. This is a requirement of EF, and has nothing to do with searching. EF won't even bother trying to generate a conceptual model of your database if it sees an entity without a key, so searching becomes impossible.

You have two options:

  1. Figure out a way to put a primary key on your table. If you have the ability to alter your table definition at all, there's no reason it shouldn't have a primary key. If there is no natural key you can pick, add a surrogate key like an identity field. It doesn't matter what your key is, it just has to be guaranteed unique for every row. It doesn't even have to be just one field: EF supports using compound keys, e.g.:

    modelBuilder.Entity<Foo>.HasKey(e => new { e.Field1, e.Field2 });

  2. Don't use Entity Framework. SQL Server has no problem querying database tables with no primary keys, assuming you can figure out how to write the queries properly. You can always use ADO.NET directly to access your database, you just won't get any of the other benefits of EF.

Michael Edenfield
  • 28,070
  • 4
  • 86
  • 117
  • I didn't accept this as the answer only because the one that I DID accept happened to provide the raw code I needed to fix my issue. That being said, your answer was incredibly informative, so I upvoted you, at the very least. If I could accept two answers, even at the cost of rep, I'd definitely give you one, too. – Kulahan Jul 30 '14 at 18:41
  • 1
    Not a problem; I prefer to avoid data annotations for schema specific aspects of my entities and use fluent api or I would have shown you basically the same thing as that answer :) – Michael Edenfield Jul 30 '14 at 21:40
2

Just a little hint for, as I know your structure from another thread LINK. You can have a primary key in your completion table, but it should consist of two keys.

It would then look like

[Table("Completion")]
public class CompletionsModel
{
    [Column(Order = 0), Key]
    public int UserId { get; set; }

    [Column(Order = 1), Key]
    public string PRD_NUM { get; set; }

    public DateTime CompletionDate { get; set; }

    public virtual CourseModel PRD { get; set; }
    public virtual StudentModel UserProfile { get; set; }
}

Primary keys really make life easier ;-)

Community
  • 1
  • 1
schlonzo
  • 1,409
  • 13
  • 16
  • This worked! I was unaware that Entity Framework wouldn't actually function at all without a PK. Though I don't need one for any of the traditional purposes, I guess this is reason enough to put one in! Thanks. – Kulahan Jul 30 '14 at 18:38
1

EF requires a table to have a primary key.
Error 159: EntityType has no key defined and other such errors.
Use linq to sql or Stored proc

phil soady
  • 11,043
  • 5
  • 50
  • 95