Need Help! Linq to Sqlite + Mono + MonoDevelop on (Ubuntu) Linux causes error at compile

[El]

I'm trying to get linq to work with sqlite on mono, in fact using
monodevelop 2.4 and mono 2.6, and Ubuntu 10.04.1 Linux. I've tried
all the suggestions in various blogs, forum, and discussions. But
nothing works. Maybe this post will get me some relief with someone
pointing out the obvious or coming about what I need to do to get it
to work. Here's what I have done so far and just can't figure what
I'm doing wrong.

In MonoDevelop, create a solution/project call sqlinqtest. I
reference most of the assemblies suggested in various sites as follows
(although I think now it is overkill):

Mono.Data
Mono.Data.Sqlite
Mono.Data.SqliteClient
System
System.Core
System.Data
System.Data.Linq

I have a sqlite 3 database call MySampleDB.sq3. I put this in my Bin/
Debug directory. I use sqlmetal to generate the MySampleDB.cs file as
follows:

$ sqlmetal /provider:Sqlite /conn "Data Source=./MySampleDB.sq3" /
code:MySampleDB.cs

Then I moved the generated cs code to the project directory and added
this file to my project in MonoDevelop.

I edited the file to rename all "Main" in the MySampleDB.cs to be
MySampleDB is avert confusion with my Main() class.

In my Main.cs on the project, which is a console type project, I
created the following code:

using System;
using System.Data;
using Mono.Data.SqliteClient;
using System.Linq;
using DBLinq;

namespace sqlinqtest
{
class MainCLass
{
public static void Main(string[] args)
{
linqtestcode();
}

private static void linqstylecode()
{
IDbConnection dbcon;
dbcon = (IDbConnection) new SqliteConnection(
"DBLinqProvider=Sqlite; DataSource=./
MySampleDB.sq3" // per other blog, does not require
//
DbLinqDataTypeProvider if pass
//
as IDbConnection.
var db = new MySampleDB(dbcon);
var users = from u in db.User select u;

foreach( var user in users )
{ Console.WriteLine(user.uid + " " + user.name); }
}
}
}

Note, I want to use the DataContext style that I am familiar with on
Linq in MS, and I want to use Linq on Mono in Linux.

So the problem I'm having is that the table column definitions in the
query is not visible and obviously I get an error at compile saying
that "Type 'User' does not contain the definition for 'uid' and no
extension method 'uid' of type 'User' could be found", suggesting that
I may have a missing "using" directive or a missing assembly
reference.

Is this a bug? If not, how do I get this to work? Thanks.

-El

[Jonathan Pryor]

On Mon, 2010-09-20 at 11:09 -0700, El wrote:
> I have a sqlite 3 database call MySampleDB.sq3.

Q1: what is the schema for this database?

> I use sqlmetal to generate the MySampleDB.cs file as
> follows:
>
> $ sqlmetal /provider:Sqlite /conn "Data Source=./MySampleDB.sq3" /
> code:MySampleDB.cs

Q2: What is generated from this command?

> Then I moved the generated cs code to the project directory and added
> this file to my project in MonoDevelop.
>
> I edited the file to rename all "Main" in the MySampleDB.cs to be
> MySampleDB is avert confusion with my Main() class.

You shouldn't have a Main class anyway for your program entrypoint, as
Main() is the name of the program entrypoint method, and Main.Main()
would be a constructor. That way lies madness. ;-)

Meanwhile, you can have a Main type and a Foo.Main() method without
naming collision.

Finally, you can use `sqlmetal --database=MySampleDB ...` to cause
sqlmetal to generate a DataContext subclass with the name MySampleDb.

> In my Main.cs on the project, which is a console type project, I
> created the following code:
>
> using System;
> using System.Data;
> using Mono.Data.SqliteClient;
> using System.Linq;
> using DBLinq;
>
> namespace sqlinqtest
> {
> class MainCLass
> {
> public static void Main(string[] args)
> {
> linqtestcode();
> }
>
> private static void linqstylecode()
> {
> IDbConnection dbcon;
> dbcon = (IDbConnection) new SqliteConnection(
> "DBLinqProvider=Sqlite; DataSource=./
> MySampleDB.sq3" // per other blog, does not require
> //
> DbLinqDataTypeProvider if pass

This is probably a casing issue, as it should be DbLinqProvider, not
DBLinqProvider (note that 'b' should be lowercase).

You're also mentioning some blog, but not the URL for said blog, which
could provide additional context.
//

> So the problem I'm having is that the table column definitions in the
> query is not visible and obviously I get an error at compile saying
> that "Type 'User' does not contain the definition for 'uid' and no
> extension method 'uid' of type 'User' could be found", suggesting that
> I may have a missing "using" directive or a missing assembly
> reference.

No, it implies that db.User is returning a User type, which is found,
but the property reference 'u.uid' cannot be found. Based on your
sqlmetal invocation, this is likely because sqlmetal is PascalCasing
your column names, and thus it should be u.Uid and u.Name (as opposed to
u.uid and u.name).

You can use the /case:leave option to partially disable this behavior.

Again, viewing the generated C# source would be helpful here [Q2].

- Jon

[El]

Hi Jon,

Thank you for the analysis. I fixed the code per your suggestion.

> Q1: what is the schema for this database?

The schema for this database is quite simple. It contains 1 table
called "user" with 2 columns - "uid" of auto increment int and "name"
of type text.

> Q2: What is generated from this command?

The output of the generated command was a class that implemented
DataContext... etc.

I also corrected the casing and use u.Uid and U.Name. For the select
declaration, at least in Monodevelop, intellisense does not show the
properties using this format:

var users = from u in db.User select u;

The properties shows up in monodevelop intellisense if I typed it as
below.

var users = from u in db.User select (new {UserId = u.Uid, FullName =
u.Name});

However, there's no intellisense on the foreach statement and the
WriteLine for the "user" properties, which follows right after the
declaration.

foreach (var user in users) { Console.WriteLine(user.UserId); }

However it does compile.

This may be just a monodevelop issue. But going back to the original
issue, after making all the corrections and regenerating the
mysampledb database using sqlmetal per your suggestion, I get a
different error when I execute it. Now it is complaining that I have
an invalid connection string. This is how I have it:

IDbConnection dbcon;
dbcon = (IDbConnection) new SqliteConnection("Data Source=./
MySampleDB.sq3; Version=3;");
var db = new MySampleDb(dbcon);

MySampleDb() is the data context class generated by sqlmetal. I
experimented with and without Version=3. None of this worked.

Is it possible for me to send you the Monodevelop solution and
database. It is a sample test. Let me know. Thanks.

-El

>  - Jon- Hide quoted text -
>
> - Show quoted text -

[El]

Hi Jon,

I think I fixed the connection string issue. I replaced

IDbConnection dbcon = (IDbConnection) new SqliteConnection("Data
Source=./MySampleDB.sq3, Version=3");

with

IDbConnection dbcon = (IDbConnection) new SqliteConnection("URI=file:./
MySampleDB.sq3, Version=3");

That took care of the problem and it now works. What I don't
understand is why the previous connection string is not working.
Isn't that also a right way to set up the connection string?

-El

> > - Show quoted text -- Hide quoted text -

[Jonathan Pryor]

On Tue, 2010-09-21 at 08:19 -0700, El wrote:
> I also corrected the casing and use u.Uid and U.Name. For the select
> declaration, at least in Monodevelop, intellisense does not show the
> properties

So this is either a MonoDevelop code completion bug, or some other bug.
Thus, the real question is this: does the following compile?

> var users = from u in db.User select u;

If it compiles without error (which I fully expect it to), then the
problem isn't sqlmetal/Linq-to-DB/etc., it's MonoDevelop. The fact that
this compiles but similarly doesn't show code completion:

> var users = from u in db.User select (new {UserId = u.Uid, FullName =
> u.Name});

further suggests that it's a MonoDevelop bug.

> Now it is complaining that I have
> an invalid connection string. This is how I have it:
>
> IDbConnection dbcon;
> dbcon = (IDbConnection) new SqliteConnection("Data Source=./
> MySampleDB.sq3; Version=3;");

The cast shouldn't be necessary, and I don't know why that would be
failing, though I see you later fixed it by using URI (which similarly
doesn't make sense...). I know that Data Source works for me, though
I've never used Version, e.g. [0].

However, you're still missing the DbLinqProvider=Sqlite parameter, so
you should be doing:

dbcon = new SqliteConnection (
"URI=file:./MySampleDB.sq3;" +
"Version=3;" +
"DbLinqProvider=Sqlite"
);

If you omit DbLinqProvider, then the DataContext will generate
MSSQL-style SQL, which DbLinq doesn't always like.

- Jon

[0] http://www.jprl.com/Blog/archive/development/mono/2009/Mar-12.html

[kwe...@gmail.com]

El
This may help, here is Linq to SQLite.
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx