Tuesday, February 14, 2017

Generate datalayer for i-nercya EntityLite 1.12.1 with PostgreSQL and .Net

I tried to install EntityLite 1.12.1 and Npgsql 3.1.9 for this. [ in .Net 4.5 targeted console app] But ended up with error:
Error 2 Running transformation: System.ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.
   at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.get_Factory()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.OpenConnection()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.SetViewsByEntity()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.Initialize()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.Render(DataLayerGeneration generation)
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.TransformText() C:\891\i_nercya_EntityLite_1121_generate\DataLayer.tt 1 1 i_nercya_EntityLite_1121_generate

In case of other ORM's similar error was resolved by DbProviderFactories entry in app.config of the same project. But in case of  EntityLite 1.12.1, I had to put   Npgsql 3.1.9 dll in gac and make DbProviderFactories entry in devenv.exe.config.

But still my datalayer was not generated. Now I got the error:

Error 2 Running transformation: System.InvalidCastException: Specified cast is not valid.
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.GetSchema(String tableOrView, DbConnection cn)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.GetSchemaUnion(EntitySetting entitySetting, DbConnection cn)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.SetFieldsMetadataByEntity()
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.Initialize()
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.Render(DataLayerGeneration generation)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.TransformText() C:\891\inercyaEntityLite1121\DataLayer.tt 1 1 inercyaEntityLite1121


EntityLite 1.12.1 was released back in September 15, 2015 and targeting .NETFramework 3.5 Client. This could have been the culprit for Specified cast is not valid. So I removed previously installed dll's in GAC and DbProviderFactories entries in devenv.exe.config. If you follow step by step actions mentioned below, you may avoid such errors I faced as mentioned above:


0. create a database ,a table as mentioned in comment section of bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/Program.cs


1. Create a project targeting .Net 3.5:


2. In this project if you try to install npgsql anything above 2.2.7, you get error like as mentioned below:

PM> Install-Package Npgsql -Version 3.0.0-beta0001 -Pre
Installing 'Npgsql 3.0.0-beta0001'.
Successfully installed 'Npgsql 3.0.0-beta0001'.
Adding 'Npgsql 3.0.0-beta0001' to i_nercya_EntityLite_1121_generate.
Uninstalling 'Npgsql 3.0.0-beta0001'.
Successfully uninstalled 'Npgsql 3.0.0-beta0001'.
Install failed. Rolling back...
Install-Package : Could not install package 'Npgsql 3.0.0-beta0001'. You are trying to install this package into a project that targets '.NETFramework,Version=v3.5', but the package does not
contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.At line:1 char:1
+ Install-Package Npgsql -Version 3.0.0-beta0001 -Pre
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Install-Package], InvalidOperationException
    + FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand


So you have to go for Npgsql 2.2.7

PM> Install-Package Npgsql -Version 2.2.7
Installing 'Npgsql 2.2.7'.
Successfully installed 'Npgsql 2.2.7'.
Adding 'Npgsql 2.2.7' to i_nercya_EntityLite_1121_generate.
Successfully added 'Npgsql 2.2.7' to i_nercya_EntityLite_1121_generate.

3. Now go for EntityLite 1.12.1 ( dependencies are resolved here):

PM> install-Package EntityLite -Version 1.12.1
Attempting to resolve dependency 'EntityLite.Core (= 1.12.1)'.
Attempting to resolve dependency 'NLog (= 2.1.0)'.
Attempting to resolve dependency 'Microsoft.SqlServer.Types (= 10.50.1600.1)'.
Installing 'NLog 2.1.0'.
Successfully installed 'NLog 2.1.0'.
Installing 'Microsoft.SqlServer.Types 10.50.1600.1'.
Successfully installed 'Microsoft.SqlServer.Types 10.50.1600.1'.
Installing 'EntityLite.Core 1.12.1'.
Successfully installed 'EntityLite.Core 1.12.1'.
Installing 'EntityLite 1.12.1'.
Successfully installed 'EntityLite 1.12.1'.
Adding 'NLog 2.1.0' to i_nercya_EntityLite_1121_generate.
Successfully added 'NLog 2.1.0' to i_nercya_EntityLite_1121_generate.
Adding 'Microsoft.SqlServer.Types 10.50.1600.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'Microsoft.SqlServer.Types 10.50.1600.1' to i_nercya_EntityLite_1121_generate.
Adding 'EntityLite.Core 1.12.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'EntityLite.Core 1.12.1' to i_nercya_EntityLite_1121_generate.
Adding 'EntityLite 1.12.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'EntityLite 1.12.1' to i_nercya_EntityLite_1121_generate.

4. Retrieve the full name of dll's:
C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Mono.Security.dll
C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Npgsql.dll
Code for retrieving name is in bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/Program.cs

5. Install these two dll's in gac:
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>gacutil -i "C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Mono.Security.dll"
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>gacutil -i "C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Npgsql.dll"

6.1 In C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe.config ( or the respective config of devenv.exe, as per your installation, you may get path for devenv.exe by viewing the properties of VS shortcut.) make DbProviderFactories entry like:
 <system.data>
        <DbProviderFactories>
            .
.
.
<remove invariant="Npgsql" />
.
.
.
 <add name="PostgreSQL Data Provider"
           invariant="Npgsql"
           description=".Net Data Provider for PostgreSQL"
           type="Npgsql.NpgsqlFactory, Npgsql, Version=2.2.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
        </DbProviderFactories>
    </system.data>

Just make sure you use correct assembly fullname as retrieved in step 4
6.2 Restart Visual studio

7. Copy paste code similar to as mentioned in the attached code's bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/DataLayer.tt. Right click on this tt file and click "Run Custom Tool".
Save DataLayer.cs generated for later use in a separate folder for future use.
8.1 Close Visual studio.
8.2 Remove DbProviderFactories customization you did in 6.1
8.3 Remve the two dll's as in step 5, from gac
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>Gacutil -u "Npgsql, Version=2.2.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>Gacutil -u "Mono.Security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756"


 So, bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/DataLayer.cs generated in step 7 is your output of this activity.

Full code available at:
bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/

Sunday, February 12, 2017

LINQ to PostgreSQL with PostgreSQL and .Net

n this example I used,
(in order)
Npgsql -Version 3.1.10
linq2db.PostgreSQL -Version 1.7.6
log4net -Version 2.0.7
CsvHelper -Version 2.16.3

Working sample code is available at bitbucket.org/hemantup/orm/src/upgrade3/LINQtoPostgreSQL175/





This post is related to : https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html
 

Saturday, February 11, 2017

MicroLite with PostgreSQL and .Net

In this example I used,

MicroLite -Version 6.3.2
Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3

Working sample code is available at bitbucket.org/hemantup/orm/src/upgrade3/MicroLite6/





This post is linked to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Friday, February 10, 2017

FluentData with PostgreSQL 9.6 and .Net 4.5

In this example I used,

Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3
FluentData -Version 3.0.1.0

Working sample code is available at:

 bitbucket.org/hemantup/orm/src/upgrade3/FluentData3/










This post is related to https://hemantrohtak.blogspot.com/2017/02/entityframework6npgsql-with-postgresql.html


Thursday, February 9, 2017

NHibernate with PostgreSQL and .Net

In this example I used,

NHibernate -Version 4.1.1.4000
Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3

1. Since I am using Sequence for Id, its worth noting that even though insert doesn't fire query to insert record immediately, but retrieves sequence.
2. Also, transaction will not be able to control rollback for sequences.
3. Actual SQL queries fired are available for review only in debug mode: setings.LogSqlInConsole = true;

For full version of code please visit: bitbucket.org/hemantup/orm/src/upgrade3/NHibernate4/








This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Wednesday, February 8, 2017

EntityFramework6.Npgsql with with PostgreSQL 9.6 and .Net 4.5

In this example I used,

Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3
EntityFramework6.Npgsql -Version 3.1.1


Two things worth noting here,
1. If I would have installed directly EntityFramework6.Npgsql -Version 3.1.1, it resolve dependency to  Npgsql -Version 3.1.0, which didn't work in my case, so I chose to go with Npgsql -Version 3.1.10

2. I had to make manual entry for DbProviderFactories in app.config to get it work.

If you get stuck somewhere, delete your bin, obj and packages folder . Then try the sample code mentioned below again.

Working sample code is available at:

 bitbucket.org/hemantup/orm/src/upgrade3/EntityFramework6/




Tuesday, February 7, 2017

OrmLite with PostgreSQL and .Net

In this example I used:
Npgsql -Version 3.1.10
ServiceStack.OrmLite.PostgreSQL -Version 4.5.6
CsvHelper -Version 2.16.3
log4net -Version 2.0.7


Sample code below gives an idea how to use OrmLite with PostgreSQL and .Net. PostgreSQL server is installed on localhost in the example below:
Visual Studio Project along with full version of code:

bitbucket.org/hemantup/orm/src/upgrade3/OrmLite/





This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html