System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: a2d1626a86bfc34b2c40d362171608eabd132e40
Title: EF 4.1: null reference exception when do savechanges of table with foreign key
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: VsDesigner Resolution: Unable_To_Reproduce
Last Modified: 2012-02-29 14:56:06
Version Found In: 1.0.79.0
Description:
I have a simple databse with two tables, one table named Authors and the other named Songs. The relationship is 0..1-*.

Well, I create the database s3db with sqlite admin, an application to create tables.

In visual studio 2010, I import references of SQLite and SQLite.Linq. Later, I create a new connection of type SQLite and create the entity model from the data base.

How it does not create the relationships, I add the relationship 0..1-*.

Later, I use ADO.NET DbContext Generator to create the classes from the model, the .tt files.

When I try to add an element to the Authors table, it works fine, I don't have problems, but when I try to add a song, I always get null reference exception when I do the saveChanges().

I think that perhaps it has relationship with the foreign key, because is the unique difference between Authors and Songs, but I indicate that a song can use a null value for the foreign key, because a song can have anonymous author.

However, in SQL Server Express 2008 I create the two tables, create the relationship, in visual studio I create the model edmx from the data base, in this cases creates the relationship too because I create it in SQL Server, I create the .tt files and I try to add the song. I use the same code, only change the data base.

In this case works fine, I can add the song without problems. So I think that the problem perhaps it is in the SQLite.Linq dll and the FK fields in EF 4.1.

The code that I use to add a new song is the following:

Songs mySong = new Songs();
mySong.Title = "my title";
myDescription = "myDescription";

Also has an IDAuthor field, but I leave in blank because in this case it has not a known author.

However, I try to set a IDAuthor too, but I still get the null reference exception.




Thanks.

<hr /><i>anonymous added on 2012-02-02 11:01:38 UTC:</i><br />
Sorry, the complete code that I use to add the new song is this:

myDbContext = new DbContext();
Songs mySong = new Songs();
mySong.Title = "my title";
mySong.Description = "description";
mySong.IDAuthor = 1;

myDbContext.Songs.Add(mySong);
myDbContext.Savechanges();

I get the error in the lat line, when I do the saveChanges().



Thanks.


<hr /><i>mistachkin added on 2012-02-06 23:38:27 UTC:</i><br />
I'm not sure what the DbContext class nor the SaveChanges method do; however, I cannot locate documentation for it on MSDN.  Could you provide a more complete example?  Does this issue involve the designer components in Visual Studio or can it be reproduced using just the code?

<hr /><i>anonymous added on 2012-02-12 19:29:49 UTC:</i><br />
Sorry for the delay.

The problem is not in the designer but in the code, so it just would be reproduced by code.

About the documentation, perhaps you could be interested in this MSDN documentation: http://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext%28v=vs.103%29.aspx or

http://blogs.msdn.com/b/adonet/archive/2011/01/27/using-dbcontext-in-ef-feature-ctp5-part-1-introduction-and-model.aspx

Really I don't know the internal implementation of the SaveChanges() method of the DbContext, but it would be very similar to the SaveChanges() of the ObjectContext of the EF 4.0.

I have been done more tests, and I have other error.

I create the model edmx like I explain in the first post, and I have an entity "Songs" that have some fields: IDSong, IDAuthor, Title...

When I do the following:

Songs mySong = new Song();
mySong.Title = "Title1";
myContext.Song.Add(mySong);
myCOntext.Savechanges();

I get an error in the saveChanges where inner exception says:

InnerException = {"SQLite error\r\ntable Songs has no column named Authors_IDAutor"}.

Inm y dataBase the name of the field is IDAuthor, and in my model edmx is the same, the field is "IDAuthor" and not "Authors_IDAuthor", so I don't know why it looks for "Authors_IDAuthor" instead of "IDAuthor".

this two errors it would be because SQLite is not full compatible with EF 4.1 and perhaps if I want to use SQLite I would use EF 4.0?



Thanks.


<hr /><i>mistachkin added on 2012-02-13 06:48:05 UTC:</i><br />
I have painstakingly attempted to reproduce the issue as you have described it so far.  Attached is the complete example project I just built that works fine.


<hr /><i>anonymous added on 2012-02-13 12:37:56 UTC:</i><br />
In this link you can get a project that present the problem that I describe:

https://skydrive.live.com/redir.aspx?cid=74f66a9bb4a4de24&resid=74F66A9BB4A4DE24!111&parid=74F66A9BB4A4DE24!110&authkey=!AOlT1F-tpyhLSRs

This application has three buttons. First one "insert author" that in hard code try to insert a song in the data base. The exception is the following:

InnerException = {"SQLite error\r\ntable Canciones has no column named Autores_IDAutor"}

The second button is the same, but try to create first an author, create a song a try to add the song to author. In this case I have not get error but the song is not inserted in the data base.

The last button is used to get all the songs that there are in the data base. In this case I get the same exception: InnerException = {"SQLite error\r\ntable Canciones has no column named Autores_IDAutor"}

The first comment that I comment in my first post, something about the foreign keys, I can't reproduce it. Since this post, I made some modifications to my application and perhaps I resolve this problem.

By the moment I would like to try to solve this error, that try to access to the Autores_IDAutor field in the Canciones table when it is not exist in any place.



Thanks.

<hr /><i>mistachkin added on 2012-02-14 01:28:34 UTC:</i><br />
I managed to get your example to run, after some manual tweaking.  However, there is some subtle problem with your entity model that I cannot figure out or fix.

The example project I attached does work and I suspect the issues you are seeing have more to do with the Entity Framework than with System.Data.SQLite itself.


<hr /><i>anonymous added on 2012-02-15 12:21:53 UTC:</i><br />
I study your attached project, but If I use the same code and the same tables in a SQL Server database, I do not get this problem.

I try to see if really I have done something wrong when I designed my model.



Thanks.

Attachments: