C#, LINQ, & MySQL

Hi -- unfortunately, I'm new to C# *and* LINQ, *and* I'm trying to learn LightSpeed at the same time :) Therefore, these might be newbie questions, but so be it . .

I went through the QuickStart and am able to save new Entities to my MySQL database.

Now I'm trying to retrieve them, but I get "Specified cast is not valid" errors. Here's some sample code:

//SETUP LightSpeed CONTEXT
var ctx = new LightSpeedContext<Contribution.ContributionUnitOfWork>();
ctx.ConnectionString = "Server=localhost;Database=xxxxx;Uid=xxxxx;Pwd=xxxxx;";
ctx.DataProvider = DataProvider.MySql5;
ctx.IdentityMethod = IdentityMethod.IdentityColumn;
ctx.PluralizeTableNames = true;

using(var work = ctx.CreateUnitOfWork())
{

    var sb = new StringBuilder(string.Empty);

        // find all Contributions with a title of "Dolphins"
        var results = work.Find<Contribution>(Entity.Attribute("Title") == "Dolphins");

        foreach(Contribution c in results)
        {
            sb.Append("Title: " + c.Title + "<br/>");
    }

    lit.Text = sb.ToString();

}

And I get an error here:

var results = work.Find<Contribution>(Entity.Attribute("Title") == "Dolphins");

(I realize this isn't LINQ, but I'll have to go one step at a time . . . thanks so much)

Michael 

P.S. This throws the same error (and I eventually want to use LINQ):

Contribution con = (
                                    from c in work.Contributions
                                    where c.Id > 0
                                    select c
                                    ).First();

This means that one of the fields LightSpeed is loading from the database doesn't match up with the type of the property or field in the C# entity declaration.

A dumb example would be if the Price column was declared in MySQL as nvarchar(...) but the Price property in C# was declared as decimal.  When LightSpeed tried to cast the string coming back from MySQL to a decimal, the cast error occurs.

Your case is probably not as obvious as this, and is probably a numeric type mismatch.  In LightSpeed 2.1 RTM, numeric type conversion was very very strict: if a property was declared in the entity as int, but came back from the database as long, a cast exception was raised.  In post-2.1 nightly builds we have made this more forgiving, so that short/int/long/decimal/float/double conversions happen automatically (obviously where permitted by the range of values of the type).

With a bit of luck the exception or inner exception will be telling you which field has the problem.  (Look for a message along the lines of "Failed to materialise field [Xxx] on...")  Have a look a the type of that field and the corresponding database column, and see if there is a possible mismatch.  (One thing we have noticed is that MySQL sometimes like to return longs where you might expect an int.)

Alternatively, grab the latest nightly from  http://www.mindscape.co.nz/products/lightspeed/nightlybuilds.aspx (Express edition) or the store (retail editions) and see if the more forgiving numeric converter fixes the problem.

If none of this helps, please send us the CREATE TABLE MySQL script and your C# entity definition and we will look into it.

(The error you get when you try to use LINQ is caused by the same underlying issue, because the LINQ query results are handled by the same loading engine.  So when we fix the first issue it will fix your LINQ error as well.) 

Thank you, Ivan.

I understand the concept and I'm ready to try it out. However, now I'm using lsgen.exe and it's creating my class files, but there seems to be a problem.

Within the "Relationships" region of Contribution.cs, I have these lines:

   [ReverseAssociation("Contributions")]
   private readonly EntityHolder<Member> _id = new EntityHolder<Member>();

I'm pretty sure the _id variable should not be the EntityHolder object.

I've checked my db schema, and all seems to be correct. In the contributions table, the "ContributorId" field is a foreign key for the members table's "Id" column.

I re-run lsgen, then I go to the LightSpeedModel.lsmodel and click "Update from source", and the seemingly erroneous property hasn't changed.

Any ideas?

Thank you,

Michael

You're right, there's something fishy going on there.  That member variable should be called something like _contributor.

Can I clarify how you are using lsgen?  Are you:

* Creating your model in the designer and running lsgen to create the C# file, or

* Running lsgen over the database to create the .lsmodel file?

If it's the former, check the association from Member to Contribution, and verify that the Backreference Name is set to "Contributor" (not blank or "Id").  Also, you should be able to use the .lsmodel.cs file that Visual Studio generates for you, rather than having to run lsgen manually, though this will probably have the same problem.

Regardless, I think that Update From Source should be spotting something awry.  Could you post the CREATE TABLE scripts for the Contributions and Members tables, and your .lsmodel file please, so we can investigate this?  Thanks!  You can attach a zip file via the Options tab.

I'd much rather not use the command line, but I'm not sure how -- please let me know.

(Meaning I've been using option 2, but would prefer option 1)

I've attached the files -- I think there's a problem with the create table scripts, as I'm specifying "ContributorId" as the name of the Foreign Key, but it seems that it may be referencing a column called "Id". This may just be a failure to understand the MySQL Administrator's UI.

Thank you, Ivan.

Michael

You're right, it is a glitch in the table definitions.  The problem is that you are specifying ContributorId as the name of the foreign key constraint and index, but Id as the actual foreign key column.  A simple foreign key declaration looks like this:

FOREIGN KEY (ContributorId) REFERENCES Members(Id)

(you don't need the CONSTRAINT clause).  The FK column is specified in the brackets.  So your declaration:

FOREIGN KEY `ContributorId` (`Id`) REFERENCES `members` (`Id`)

creates a foreign key which, although it is *named* ContributorId, actually specifies that the *Id* column is a foreign key (to the Members table).  The subtle distinction is between the name of the key and the name of the column which the key operates on -- hope that makes sense!

In MySQL Administrator, the thing to watch out for is the table on the right of the Foreign Keys tab, under the "Ref table" drop-down.  Here you need to set the Column to the foreign key column -- in this case ContributorId.  (The Reference Column is the PK column of the referenced table, i.e. Id, just as you have it at the moment.)

Regarding not using the command line, you can create a .lsmodel file in Visual Studio by right-clicking the project, choosing Add > New Item, selecting the Data tab and selecting the LightSpeed Model icon.  You can then drag tables from Server Explorer onto the design surface.  (You will need to install MySQL Connector/NET if you haven't already -- make sure you get the 5.x version and not the 1.x version which seems to be bizarrely more prominent!)  Visual Studio will generate and maintain a C# file behind the scenes as you edit the model, and automatically includes this file in your project (you will be able to see this file if you expand the + sign against the .lsmodel file).

You should therefore not need to run the lsgen command line unless you need to script it, for example as part of a build process.

Hope this helps!