Using Microsoft.Data.SqlClient with Entity Framework 6

[lcava]

I apologize in advance but I have not found where to write. I need the "SQLite and SQL Server Compact Toolbox" for visual studio 2013, on the download page I read: "If you need a Visual Studio 2010 version of this extension, please contact me via Github / email" but I haven't found where to contact her . How can I do? Thanks and sorry for the surgery

Luca

[ErikEJ]

You could have used the SqlCeToolbox github repo for this issue - but you can find the release here: https://github.com/ErikEJ/SqlCeToolbox/releases/tag/vs2012

[lcava]

Thanks a lots

Regards Luca

Il Mer 13 Ott 2021, 14:02 Erik Ejlskov Jensen @.***> ha scritto:

You could have used the SqlCeToolbox github repo for this issue - but you can find the release here: https://github.com/ErikEJ/SqlCeToolbox/releases/tag/vs2012

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ErikEJ/erikej.github.io/issues/35#issuecomment-942229985, or unsubscribe https://github.com/notifications/unsubscribe-auth/AH7XBZOMNVAY64ZQ5QNDVF3UGVYOJANCNFSM5BYSWWZQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[ShahidRasheed]

Hi Erik, I installed your package but I keep getting following exception

Unhandled Exception: System.IO.FileLoadException: The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047) at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type)

I am using following

• Net Framework 4.7.2
• Microsoft.Data.SqlClient 4.1

Thanks,

[ErikEJ]

@ShahidRasheed Please create an issue here with more details: https://github.com/ErikEJ/EntityFramework6PowerTools/issues

[ShahidRasheed]

Thanks, @ErikEJ I have created an issue here https://github.com/ErikEJ/EntityFramework6PowerTools/issues/98

[KnechtLRK]

Hi Erik,

I installed the latest package. I'm using .Net Framework 4.8 and I'm getting the following error reported against the .edmx file

Error 175: The ADO.NET provider with invariant name 'Microsoft.Data.SqlClient' is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details.

I followed the instructions in the ReadMe section modifying the app.config updating the EntityFramework provider, adding the DbProvidersFactories, and updating the provider in the connection string.

 <entityFramework>
    <providers>
      <provider invariantName="Microsoft.Data.SqlClient" type="System.Data.Entity.SqlServer.MicrosoftSqlProviderServices, ErikEJ.EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <add name="SqlClient Data Provider"
        invariant="Microsoft.Data.SqlClient"
        description=".NET Framework Data Provider for SqlServer"
        type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient" />
    </DbProviderFactories>
  </system.data>

I also updated the .edmx file StorageModels Schema section to the following:

<Schema Namespace="Common.Models.Store" Provider="Microsoft.Data.SqlClient" ProviderManifestToken="2012.Azure" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">

Any suggests would be greatly appreciated. Thank you!!

[ErikEJ]

@KnechtLRK Please create an issue here with more details: https://github.com/ErikEJ/EntityFramework6PowerTools/issues. Also, what is the full stack trace including the innner exception, please

[KnechtLRK]

@ErikEJ I ended up getting this working. The situation was we have a separate project for the entity framework models which is a class library. It was this project that was reporting the error. After adding the ErikEj.EntityFramework.SqlServer package to the console application the console application does not have the same build error and even though the class library still reports the build error the application does run successfully.

[MartinInnovend]

Hello Erik (or someone else who can help me),

We have migrated our software from the old EF6 (with old driver) to the new ErikEJ NuGet package (6.6.3) with new Microsoft Data driver (Microsoft.Data.SqlClient 5.01).

Now, everything went fine, till the moment that we moved all our .Net 4.8 software (incl. sql server azure databases with exactly the same configuration) from our old Azure environment to our new Azure environment. Suddenly, some select queries became extremely slow, even ending in a timeout (after 30-60 sec.). The strange thing, however, is that implementing these queries as normal Ado.Net plain sql queries, is superfast (< 1 sec.) on the new environment. I even used the exact same sql that is generated by the EF (ErikEJ) component, So the difference is not in the query output sql.

The select query is not really that special. And was reasonable quick in our old Azure database. Just not as quick as Ado.Net. The query is just a straightforward 8 table join with a where clause with 7 expressions in it. Also the number of result records is not big, less than 1.000 records.

We are wondering why there can suddenly be such a big performance difference ? Because in the end it is always sql executed on the server, right ? Why is Ado.Net so extremely much faster ? And can it have to do with record or table locking ? Or isolation levens or something ? It looks like the parsing of the Linq query to sql (before query.ToList is executed) is slow. Plus the executing on db with ToList() after parsing is also slow. Or are there some other extra sql statements generated with sql server that are not used with Ado.Net ? (I used Ado.Net inside a EF db connection, like: List result = ctx.Database.SqlQuery(sql);

// Our EF query (anonymised, but just to give you an idea): var query = ( from s in ctx..... join cb in ctx.... on s....Id equals cb.ID join srp in ctx..... on s.ID equals srp......ID join p in ctx.Products on srp....ID equals p.ID

                    join cl in ctx...... on p....ID equals cl.....ID into cs1 
                    from cl in cs1.DefaultIfEmpty() // left join

                    join l in ctx..... on s.....ID equals l.....ID into cs2
                    from l in cs2.DefaultIfEmpty() // left join

                    join c in ctx..... on s....ID equals c.ID
                    join il in ctx.ImagesList on p.ImageID equals il.ID

                    join snp in ctx.... on srp.....ID equals snp.ID into cs3
                    from snp in cs3.DefaultIfEmpty() // left join

                    where
                        (s... == ...Id) && 
                        ((s.....Date >= periodStart) && (s.....Date < periodTill)) && 
                        ((cb.... == ...Nr) || (...Nr == null)) &&                          
                        (cl....Order == 1) &&
                        (l.Order == 1) && 
                        (cl...ID == c.....ID) &&
                        (p.IsActive == true)

result = query.ToList();

[ErikEJ]

@MartinInnovend

Now, everything went fine, till the moment that we moved all our .Net 4.8 software (incl. sql server azure databases with exactly the same configuration) from our old Azure environment to our new Azure environment.

I assume this is the main point and this does not seem to be related to your .NET software, as no changes were made?

Then it could be a bad query plan chosen for the first query that hit your new database, maybe with unusual parameter values.

You can force a node swith by scaling your database down or up.

[MartinInnovend]

@ErikEJ

The query sql and the parameter values set for the plain sql Ado . Net query are 100% identical to the query as was generated by EF. So it doesnt seem to be the query plan. The question remains what is then the difference between the two ?

I found that the sql server setting 'arithabort' matters big time in performance. By default In SSMS this settings is ON (every time good performance). While this setting is default OFF under EF (always bad performance).

In SSMS: set arithabort off -> very bad performance. When omitting this setting, its very fast.

Is there a simple way to set this setting before executing a EF query ? I found some examples on Internet, but didnt work.

[ErikEJ]

@MartinInnovend As I said this confirming my assumption about bad parameter sniffing

https://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query

[MartinInnovend]

@ErikEJ,

Ok, lets assume that the problem is parameter sniffing. Then, what can we concrete do about this ? Since its something on the sql server level, not application level, with EF code (the EF component now generates a dynamic sql statement by the way with 'exec sp_executesql').

Is there something concrete in the component that we can use ? Or do we have to write a sort of 'hack' like with a custom interception for the DbConfiguration of a DbContext ? Where we add a sql statement just before or after the actual sql statement ? Like e.g. " option(recompile)" or "OPTIMIZE FOR UNKNOWN" or something ? Or apply 'set arithabort on' in some way for the EF / db session ? And if so, how exactly ?

Or use a local variable instead of a query parameter value ? https://stackoverflow.com/questions/25145667/ef-6-parameter-sniffing

And also still the question: why is it that a direct Ado.Net sql query without EF always takes a fast execution plan ? Just luck ? (And why would the setting 'ARITHABORT' make such a big difference ?)

[ErikEJ]

@MartinInnovend I would take a step back and see if I could create better indexes to help SQL Server always choose an optimal plan.

[MartinInnovend]

@ErikEJ I think you were correct: I added some indexes on the query tables, and now the EF query is fast as well.

It remains a bit of vague to me what Sql Server sometimes does under the hood, but anyway, problem solved.

Thanks for your help.

[MartinInnovend]

For all the people who use the Microsoft.Data.SqlClient EF6 NuGet package (v6.6.2, for .Net 4.x), and get the error message 'Unable to load DLL 'Microsoft.Data.SqlClient.SNI.x64.dll' I found a solution yesterday:

Whats the problem ? The ErikEJ.EntityFramework.SqlServer.dll uses Microsoft.Data.SqlClient.dll. But the latter also has dependencies to some (2?) native Microsoft dll's, which may not always be published to the output bin folder as well (namely Microsoft.Data.SqlClient.SNI.x86.dll and/or Microsoft.Data.SqlClient.SNI.x64.dll).

The main .Net assembly of the package (Microsoft.Data.SqlClient.dll) is simply published to the output bin directory by adding a (NuGet package dll) reference to you (.exe) project, but the other native Microsoft SqlClient dlls may not be automatically deployed as well.

I that case, to make sure those dll's are also automatically copied to the output bin folder:

1.) Add these (MSBuild) lines to the host project .csproj:

1a.) Add this ItemGroup:

1b.) Add this Target: <Target Name="CopyMissingOutputFiles" Condition="'$(WebProjectOutputDir)' != ''" >

<Copy SourceFiles="@(OurMissingOutputFiles)" 
      DestinationFiles="@(OurMissingOutputFiles -> '$(WebProjectOutputDir)%(RecursiveDir)%(Filename)%(Extension)')" />

In this case, I used version 5.0.1 of Microsoft.Data.SqlClient, but that can differ of course, so check if this version / directory exists first. The name 'OurMissingOutputFiles' can also be any name of course.

The 'WebProjectOutputDir' in the output path is a MSBuild reserved word that works for Web projects (like WebApi or Mvc). For a desktop application (like WPF or so), you might need a slightly different word, like OutputPath or OutDir.

Hope this helps.

[MartinInnovend]

Code changes in .csproj:

1a.) Add this ItemGroup:

  <ItemGroup>
        <OurMissingOutputFiles Include = "..\packages\Microsoft.Data.SqlClient.5.0.1\lib\net462\Microsoft.Data.SqlClient.SNI.x86.dll"/>
        <OurMissingOutputFiles Include = "..\packages\Microsoft.Data.SqlClient.5.0.1\lib\net462\Microsoft.Data.SqlClient.SNI.x64.dll"/>
  </ItemGroup>

1b.) Add this Target:
<Target Name="CopyMissingOutputFiles"
        Condition="'$(WebProjectOutputDir)' != ''" >

    <Copy SourceFiles="@(OurMissingOutputFiles)" 
          DestinationFiles="@(OurMissingOutputFiles -> '$(WebProjectOutputDir)%(RecursiveDir)%(Filename)%(Extension)')" />
  </Target>

[ErikEJ]

What if you add an explicit reference to the MDS package from your executable project @MartinInnovend ?

[MartinInnovend]

@ErikEJ I do already have a reference to the Microsoft.Data.SqlClient.dll (via NuGet package). I cant add a reference to the Microsoft.Data.SqlClient.SNI dll's, since those are not .Net assembly dll's (I get that error message in VS when I try).

[ErikEJ]

@MartinInnovend IT would be great if you could share a repro - it is not something I have come across, but .aybe it is .NET Framework specific?

[MartinInnovend]

@ErikEJ I dont think I can create a test project to reproduce the problem, since locally everything goes well: after a build (in VS), all dll's are placed in the bin directory. So I dont think it's a code problem.

The problem occurs at deploy time. We deploy to Azure (.net api and mvc .net framework 4.x). Then the dll's are not automatically copied to the bin output dir. Its a pretty straigtforward deployment process to deploy a web application to a AppService, without extra deployment frameworks. Just a simple Azure pipeline (for a web project).

[ErikEJ]

@MartinInnovend - at the end of the day, you are creating a .zip file in your build that you could share.

You could also share the deployment task - there are many deployment options, so I don't think the term "straight forward" is suitable here.

[memnarch17]

Hi, Eric. Would like to ask if my error has something to do with your package and if I miss anything.

So currently, we are using the usual System SqlClient. We are changing to Azure Active Directory Service Principal authentication and I did the migration from System.Data.SqlClient to Microsoft.Data.SqlClient. I did necessary changes and installed your package as well and I did the provider update and the dbconfiguration. I tried running it, and I'm getting this error: Exception Details: System.ArgumentException: Invalid value for key 'authentication'.

Did I miss something or is this error not related with your package?

[ErikEJ]

@memnarch17 Please create an issue with details in the repo here - a random error message is not helpful in troubleshooting.

Could be related to this, but impossible for me to say since you are not sharing ANY details: https://github.com/ErikEJ/EntityFramework6PowerTools/issues/130

[memnarch17]

Hi, @ericej. I already know what caused it. After reviewing your app.config, I noticed that our config file under the entityframework tag has a defaultconnectionfactory before the provider tag. I removed it and tried it again, and it finally worked. Thanks for the help!

[jwdaigle]

Hi @EricEj - I am hoping you still have some love for Microsoft.Data.SqlClient and your "shim" thats sits in between allowing EF 6 to use Microsoft.Data.SqlClient. A while back we had a requirement/need to authenticate to SQL Azure using AppId/ClientSecret. Your pkg worked REALLY well for that, thank you.

We now have a need to secure our network perimeter around SQL Azure so need to not enable "Public Network Access" in the Networking settings in Portal.

Once we Disable this setting, we ARE able to access a DB using a private endpoint using SSMS 19, but UNABLE to access it using the ErikEJ pkg with the same connection string (with SQL Authentication). It specifically says that Public Network access is disabled, and that wont work.

Are you aware of this issue? Is there already a fix for it?

Thank you for any guidance!

Joe

[ErikEJ]

@jwdaigle that sounds like a firewall issue and unrelated to the driver.

Feel free to open an issue in the Github repo with details including the exact error message you are seeing.

[jwdaigle]

Would be, but SSMS works just fine...

Get Outlook for Androidhttps://aka.ms/AAb9ysg

---

From: Erik Ejlskov Jensen @.> Sent: Wednesday, April 24, 2024 1:34:18 PM To: ErikEJ/erikej.github.io @.> Cc: jwdaigle @.>; Mention @.> Subject: Re: [ErikEJ/erikej.github.io] Using Microsoft.Data.SqlClient with Entity Framework 6 (#35)

@jwdaiglehttps://github.com/jwdaigle that sounds like a firewall issue and unrelated to the driver.

Feel free to open an issue in the Github repo with details including the exact error message you are seeing.

— Reply to this email directly, view it on GitHubhttps://github.com/ErikEJ/erikej.github.io/issues/35#issuecomment-2074066582, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAH3HDO5ZMV2CZBHUVYZ2QDY6475VAVCNFSM5BYSWWZ2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBXGQYDMNRVHAZA. You are receiving this because you were mentioned.Message ID: @.***>

[ErikEJ]

@jwdaigle If you want help, I need you to create an issue, with for example your connection string and the exact error message, please!

[jwdaigle]

ok, no problem. Github issue I assume you mean

---

From: Erik Ejlskov Jensen @.> Sent: Wednesday, April 24, 2024 3:36 AM To: ErikEJ/erikej.github.io @.> Cc: jwdaigle @.>; Mention @.> Subject: Re: [ErikEJ/erikej.github.io] Using Microsoft.Data.SqlClient with Entity Framework 6 (#35)

@jwdaiglehttps://github.com/jwdaigle If you want help, I need you to create an issue, with for example your connection string and the exact error message, please!

— Reply to this email directly, view it on GitHubhttps://github.com/ErikEJ/erikej.github.io/issues/35#issuecomment-2074277841, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAH3HDMZ3WXTVQEEM2RYCALY65OIFAVCNFSM5BYSWWZ2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBXGQZDONZYGQYQ. You are receiving this because you were mentioned.Message ID: @.***>

[ErikEJ]

@jwdaigle Yes, here: https://github.com/ErikEJ/EntityFramework6PowerTools/issues

[jwdaigle]

Doing it now. Thank you in advance for any help/guidance you can provide.

---

From: Erik Ejlskov Jensen @.> Sent: Wednesday, April 24, 2024 5:34 AM To: ErikEJ/erikej.github.io @.> Cc: jwdaigle @.>; Mention @.> Subject: Re: [ErikEJ/erikej.github.io] Using Microsoft.Data.SqlClient with Entity Framework 6 (#35)

@jwdaiglehttps://github.com/jwdaigle Yes, here: https://github.com/ErikEJ/EntityFramework6PowerTools/issues

— Reply to this email directly, view it on GitHubhttps://github.com/ErikEJ/erikej.github.io/issues/35#issuecomment-2074521558, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAH3HDNCEF3I6TUJ37ZOR5DY654C3AVCNFSM5BYSWWZ2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBXGQ2TEMJVGU4A. You are receiving this because you were mentioned.Message ID: @.***>

[ErikEJ]

An official provider based on my effort is now available in preview - please start adopting it, as my provider will be deprecated soon: https://www.nuget.org/packages/Microsoft.EntityFramework.SqlServer/

[jwdaigle]

Done.

---

From: Joe Daigle @.> Sent: Wednesday, April 24, 2024 5:35 AM To: ErikEJ/erikej.github.io @.>; ErikEJ/erikej.github.io @.> Cc: Mention @.> Subject: Re: [ErikEJ/erikej.github.io] Using Microsoft.Data.SqlClient with Entity Framework 6 (#35)

Doing it now. Thank you in advance for any help/guidance you can provide.

---

From: Erik Ejlskov Jensen @.> Sent: Wednesday, April 24, 2024 5:34 AM To: ErikEJ/erikej.github.io @.> Cc: jwdaigle @.>; Mention @.> Subject: Re: [ErikEJ/erikej.github.io] Using Microsoft.Data.SqlClient with Entity Framework 6 (#35)

@jwdaiglehttps://github.com/jwdaigle Yes, here: https://github.com/ErikEJ/EntityFramework6PowerTools/issues

— Reply to this email directly, view it on GitHubhttps://github.com/ErikEJ/erikej.github.io/issues/35#issuecomment-2074521558, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAH3HDNCEF3I6TUJ37ZOR5DY654C3AVCNFSM5BYSWWZ2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBXGQ2TEMJVGU4A. You are receiving this because you were mentioned.Message ID: @.***>