Monday, October 19, 2015

Custom Linq Provider: Enabling a Data Source for LINQ Querying

These are msdn articles explaining the steps in detail about building a custom linq query provider, but be prepared to run through a highly complex framework before reading them.

https://msdn.microsoft.com/en-us/library/bb882640.aspx
http://blogs.msdn.com/b/mattwar/archive/2007/07/30/linq-building-an-iqueryable-provider-part-i.aspx

http://blogs.msdn.com/b/mattwar/archive/2008/11/18/linq-links.aspx

This a really helful source  code of sample project which implements few custom linq providers for different data source types sql ce, mysql, access, sqllite, etc.

http://iqtoolkit.codeplex.com/

The source code above works with sql 2005, however current sql versions do not support .mdf file attachments through code, hence after migrating the Northwind database to SQL 2014 version, I had to change DbEntityProvider.From method as below

if (provider == null)
            {
                var clower = connectionString.ToLower();
                // try sniffing connection to figure out provider
                if (clower.Contains(".mdb") || clower.Contains(".accdb"))
                {
                    provider = "IQToolkit.Data.Access";
                }
                else if (clower.Contains(".sdf"))
                {
                    provider = "IQToolkit.Data.SqlServerCe";
                }
                else if (clower.Contains(".sl3") || clower.Contains(".db3"))
                {
                    provider = "IQToolkit.Data.SQLite";
                }
                else if (clower.Contains(".mdf"))
                {
                    provider = "IQToolkit.Data.SqlClient";
                }
                else
                {
                    //throw new InvalidOperationException(string.Format("Query provider not specified and cannot be inferred."));
                    provider = "IQToolkit.Data.SqlServer";
                }

And SqlServerQueryProvider class like:

public static string GetConnectionString(string connectionString)
        {
            //return string.Format(@"Data Source=.\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=true;AttachDbFilename='{0}'", databaseFile);
            //return string.Format(@"Data Source=.\QLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=true;AttachDbFilename='{0}'", databaseFile);
            return connectionString;
        }

Also I had to pass the actual connection string to  DbEntityProvider.From method like:

var provider = DbEntityProvider.From("Data Source=<SQL Server Name>;Initial Catalog=Northwind;Integrated Security=True", "Test.NorthwindWithAttributes");

No comments:

SSL Error - The connection for this site is not secure

 After cloning a git repo of dot net framework website and trying to run it all I could see was this error Turns out the fix was to simply e...