SQLite LinqConnect First or FirstOrDefault

[toSilence]

Hi,

I'm trying to use LinqConnect with an SQLite database in C#.

Querys with linq are working generally but if i use 'First' or 'FirstOrDefault' i get an exception:
var firstValue = context.Customer.First(x => x.Company == "test1");
var firstOrDefaultValue = context.Customer.FirstOrDefault(x => x.Company == "test1");

ErrorMessage:
SQLite error near ".": syntax error

StackTrace:
at Devart.Data.SQLite.a7.a(String A_0, UInt32 A_1, String& A_2)
at Devart.Data.SQLite.bs.p()
at Devart.Data.SQLite.ae.e()
at Devart.Data.SQLite.SQLiteDataReader.c()
at Devart.Data.SQLite.SQLiteCommand.InternalExecute(CommandBehavior behavior, IDisposable statement, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.Table`1.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
at System.Linq.Queryable.First[TSource](IQueryable`1 source, Expression`1 predicate)

Any help appreciate.

Thx in advance.

BR

[MariiaI]

StackTrace:
at Devart.Data.SQLite.a7.a(String A_0, UInt32 A_1, String& A_2)
at Devart.Data.SQLite.bs.p()
....
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)

Seems that you are using LinqConnect (SQLite) in some project where Microsoft SqlClient was previously used, thus, please check that you are using the LinqConnect generated files, correct connections strings, etc.
Please also send us a small test project with test database, with which this error occurs, so that we are able to investigate this issue in more details and find the solution for you in a shortest time.

[toSilence]

Hi,

I sended a small test project to your support email adress.

BR

[MariiaI]

Thank you for the test project. The error you are getting is an expected one.
In your project, you are trying to use Devart.Data.SQLite.SQLiteConnection with standard LINQ to SQL classes (DbClassesDataContext), which support only Microsoft SQL Server and SqlClient.

LINQ to SQL (*.dbml model) is a standard Microsoft' ORM solution while LinqConnect is our own ORM solution, closely compatible to Microsoft LINQ to SQL. LinqConnect includes a lot of features that are not available in other ORM solutions, e.g. LINQ to SQL:
http://www.devart.com/linqconnect/features.html
http://www.devart.com/linqconnect/linq- ... sions.html

LinqConnect uses its own classes since version 4.0 (since this version the references to System.Data.Linq are removed, LinqConnect uses only its own classes).
For correct work with LinqConnect it is necessary to use Devart.Data.Linq instead of System.Data.Linq.
Please refer to http://forums.devart.com/viewtopic.php? ... 69#p110744

For comparison, LINQ to SQL classes:

Code: Select all

[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="BendexShop")]
	public partial class DbClassesDataContext : System.Data.Linq.DataContext
	{
		
		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
		....
		public System.Data.Linq.Table<Customer> Customer
		{
			get
			{
				return this.GetTable<Customer>();
			}
		}
		
	}

LinqConnect classes:

Code: Select all

    [Devart.Data.Linq.Mapping.Database(Name = "main")]
    [Devart.Data.Linq.Mapping.Provider(typeof(Devart.Data.SQLite.Linq.Provider.SQLiteDataProvider))]
    public partial class MainDataContext : Devart.Data.Linq.DataContext
    {
        public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(MainDataContext));
        private static Devart.Data.Linq.Mapping.MappingSource mappingSource = new Devart.Data.Linq.Mapping.AttributeMappingSource();
     ....
	 
	 public Devart.Data.Linq.Table<Customer> Customer
        {
            get
            {
                return this.GetTable<Customer>();
            }
        }
   }

To be able to work with LinqConnect and SQLite, please create new LinqConnect model and use the generated DataContext and entity classes. LinqConnect documentation is available:
1) http://www.devart.com/linqconnect/docs/?
2) in the Entity Developer documentation: ORM Support -> LinqConnect
JIC: Entity Developer documentation is available with the installation package (the "Help" component should have been selected when installing).

LinqConnect samples are available in the "C:\Program Files (x86)\Devart\dotConnect\Linq\Samples\" folder after installing LinqConnect (if the Samples component has been selected during installation).

[toSilence]

Hi,

thank you for your very well and comprehensive explanation.
Its clear now that we can't workaround your datacontext.
The problem is that we have an existing and very complex datacontext which would not be easily 'migrated' to your datacontext.
And it seems to be too much effort to update both datacontexts to our needs.

Maybe there was an easy solution before version 4.0, but i think this version is no longer supported, right?

I will try what you suggested and will make a new testing project with your datacontext.

Thank you very much for your help.

Best Regards

[MariiaI]

The problem is that we have an existing and very complex datacontext which would not be easily 'migrated' to your datacontext.

You can use the LinqConnect Migration Wizard for this (Tools-> LinqConnect-> Migration wizard), which allows migrating from a LINQ to SQL model to a LinqConnect model.
After completing the steps of the LinqConnect Migration Wizard and getting the *.lqml file, you should do the following in your application:
- open the model (*.lqml file);
- select "New Template" from the shortcut menu of the "Templates" node in Model Explorer and then select the "LinqConnect" template;
- to specify the database connection open Database Explorer and right-click on Database Connection-> Edit Connection Properties;
- check the Target Server in Model Settings -> Synchronization -> Mapping (it must be set to the one that you are going to use (i.e., to SQLite));
- select "Regenerate Storage and Mapping" from the diagram shortcut menu for regenerating model parts, specific to the database server (i.e., to SQLite);
- save changes to generate the code;
- run the Update Database Wizard to create the database objects, if necessary;

After saving the model, the code for DataContext and its entities should be generated.
Some useful information can be found here:
http://forums.devart.com/viewtopic.php?t=28383
http://forums.devart.com/viewtopic.php?t=27145#p91655

We recommend you to create a new test app, add there your *.dbml model and try performing the LinqConnect Migration Wizard to see if this could help in your scenario.

I will try what you suggested and will make a new testing project with your datacontext.

If you encounter any issues with this, feel free to contact us.