none
SQL Compact 3.5 and Entity Framework

    Question

  • Hi,

    I have a compact SQL 3.5 database that I am using with EF 4.0. I have it working and pulling data by using the "AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting",true);" workaround.  However, I cannot save new records because of the identity issue .  I have tried a few things, including completely deleting my GUID field and changing my primary key (ID) field properties to no longer be an identity column. Not sure what I am missing. 

    At this point, I can go one of 2 ways: 

    1. fix this issue in 3.5...any help with how I need to configure the table to get this to work would be much appreciated!  Everything works until I call "db.SaveChanges();", then I get an error "server-generated keys and server-generated values are not supported by SQL server Compact". When looking at the properties of the table I have: Allow Nulls=No,  Identity=false, RowGuid=false, Unique=No, Primary Key=No.  Not sure why this doesn't work? There are no other identity columns in this table, although there ARE identity columns in other tables (but I am not trying to save to those columns)

    2. I can attempt to convert to 4.0.  HOWEVER, my program depends on RDA to sync with SQL Server Express.  Sql Server Express will then use Replication to sync with SQL Server.  I was previously using a 6.5 windows mobile app with 3.5 and everything was working wonderfully.  We are now trying to convert to a tablet using MVC and entity framework and are having these issues.  I want to remain using my already-working programs, if at all possible.   This means I want to use my SqlCeRemoteDataAccess objects to pull and push.  If I use the suggestions here:  http://erikej.blogspot.com/search?q=%28SqlCeRemoteDataAccess  has anyone encountered a problem with this kind of configuration utilizing RDA and replication? 

    If there are a lot of problems, then I will have to weigh fixing the issue with #1 or #2.  Any advice would be helpful!

    EDIT: I tried using my RDA program on a 4.0 database and am getting an "Incompatible database" error

    Thanks,

    Lisa


    • Edited by lisat23 Monday, July 01, 2013 4:01 PM
    Sunday, June 30, 2013 10:26 PM

Answers

All replies

  • Suggest you script the CREATE TABLE statement for the table in question, so we can see all column definitions. Then we might be able to get #1 to work. And you need a GUID primary key in your table.

    Please mark as answer, if this was it. Visit my SQL Server Compact blog Windows 8 Apps Showcase

    Monday, July 01, 2013 7:53 AM
    Moderator
  • OK, I have the SQLServer Compact Toolbox in VS2010.  I did a create script and the relevant values are:

    CREATE TABLE [myTable] (
      [ID] int NOT NULL

    , [GUIDid] uniqueidentifier NOT NULL
    );
    GO
    ALTER TABLE [myTable] ADD CONSTRAINT [PK__myTable__00000000000001AE] PRIMARY KEY ([ID]);
    GO
    CREATE INDEX [IX_otherTblID] ON [myTable] ([otherTblID] ASC);
    GO

    Because this is a related table, I also took the Identity specification off of the other table.  The relevant values are:

    CREATE TABLE [myTable] (
      [ID] int NOT NULL

    , [GUIDid] uniqueidentifier NOT NULL
    );
    GO
    ALTER TABLE [myTable] ADD CONSTRAINT [PK__myTable__0000000000000146] PRIMARY KEY ([ID]);
    GO

    I have checked over the other fields, and they are mainly nvarchar(int) values or datetime values...none with an identity.

    As a note, I am using RDA to create this database. I am using "TrackingOnWithIndexes".  However, when messing with it yesterday, I changed it to "Tracking Off".  Let me know if I need to alter these.

    Due to these changes, I am trying to execute the following code:

                    int lastID = db.myTable.OrderByDescending(u => u.ID).FirstOrDefault().ID;
                    theEFobject.ID = lastID + 1;
                    db.myTable.Add(theEFobject);
                    int recordsAffected = db.SaveChanges();

    If I run this with our compact framework I get the error message "server-generated keys...".  However, if I change the database connection back to the SQLExpress instance, I get an error about the ID cannot be NULL for this table.  I thought I set the ID before calling SaveChanges!  Am I missing something with EF and just getting the "server-generated keys..." error as a default?

    Thanks for your help.  I will continue trying things here, too, and update this with an "EDIT:" if something changes

    Monday, July 01, 2013 10:48 PM
  • Are you using Code First? Suggest you remove the two ID columns altogether, they are not needed and probably the cuaee for your issues.

    Please mark as answer, if this was it. Visit my SQL Server Compact blog Windows 8 Apps Showcase

    Tuesday, July 02, 2013 6:53 AM
    Moderator
  • Yeah, we are using Code First.  Unfortunately I can't take the ID columns off...some of the tables have been used in other programs for years and the GUID was just an addition once we started working with mobile.  The progam actually runs on SQL, then we change the data connection to SQLcompact for it to run on a tablet.   After looking into it, I agree that the ID column is probably the issue.  It seems that EF will automatically assign an ID column as an identity specification with Code First.  Although I change it in the database, I think the EF attributes are being looked at before the database schema is looked at.  I will attempt to take this attribute off of the ID field and let you know what happens.
    Tuesday, July 02, 2013 2:01 PM
  • You can override this default behaviour by adding a

    [Key]

    Attribute to the guidid column, I think.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog Windows 8 Apps Showcase

    • Marked as answer by lisat23 Wednesday, July 03, 2013 10:50 PM
    Tuesday, July 02, 2013 2:13 PM
    Moderator
  • Great!  I will try that, also.  I was able to take the identity specification off with the following global code:

    public class YourContext : DbContext {
        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            modelBuilder.Conventions.Remove<StoreGeneratedIdentityKeyConvention>();
        }
    }

    I can also turn it on or off with the following attribute for each field definition:

    public class MyEntity {
        [DatabaseGenerated(DatabaseGeneratedOption.None)];
        public int Id { get; set; }        
    }

    After that, I was able to seed the ID myself and 3.5 works!  I will have to play with it to figure out what columns I am going to push/pull so that replication is properly executed with multiple devices, but you have really helped to point me in the right direction...thanks!

    Wednesday, July 03, 2013 10:50 PM