question

KurtK-5456 avatar image
0 Votes"
KurtK-5456 asked KurtK-5456 commented

Does package microsoft.entityframeworkcore.sqlserver work with xamarin.forms?

I am getting an error when trying to connect. I am only testing with the xamarin.ios project. I realize that connecting to a sql database over the internet is not a good idea. However, in my case the application connects only to a local database on a company network using a wifi connection.

Error when running with the ios simulator:

System.TypeInitializationException: 'The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception.'

2020-11-27 13:54:11.795508-0800 TmMobile.iOS[22564:1043002]
Unhandled Exception:
System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNILoadHandle' threw an exception. ---> System.DllNotFoundException: sni.dll assembly:<unknown assembly> type:<unknown type> member:(null)
at (wrapper managed-to-native) Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(intptr)
at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize () [0x00000] in H:\tsaagent4_work\2\s\src\Microsoft.Data.SqlClient
etcore\src\Interop\SNINativeMethodWrapper.Windows.cs:289
...

dotnet-xamarinformsdotnet-entity-framework-core
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnthonySoto-MSFT avatar image
1 Vote"
AnthonySoto-MSFT answered KurtK-5456 commented

Hi Kurt,

Unfortunately, this scenario is unsupported for Xamarin.Forms -> Xamarin.iOS. For Mobile platforms, the best practice (as mentioned in this thread) is to create an intermediate service to then connect to your database. Connecting directly through a mobile platform is heavily discouraged, so you will not find many resources or much supportability for this scenario.

The Microsoft.Data.SqlClient.SNI.dll assembly is a Windows only assembly (Native networking implementation), which does not apply to the iOS platform. The DLL is used by Windows targeted SqlClient binaries (runtimes\win in NuGet package), so when implementation is on a non-Windows platform, the assembly is going to throw a DLLNotFound Exception when trying to use native Windows SNI.

The SNI runtime binary dependency from the SqlClient package is attempting to target the native SNI assembly. There is the possibility of using Managed Networking by setting this AppContext switch in app code, but testing this proved to not have any effect on the missing SNI assembly error.
Source: https://docs.microsoft.com/en-us/sql/connect/ado-net/appcontext-switches?view=sql-server-ver15#enabling-managed-networking-on-windows

Aside from the issues with SNI when on the emulator, the error message being received on the physical device build is due to a Xamarin.iOS limitation with NetStandard. Xamarin.iOS must use AOT compilation, due to the lack of support for dynamic code generation on the iOS platform. This is why we are seeing AOT mentioned in the error, but regardless, there is a dependency on "System.Configuration.dll" from the SQLServer package and its dependencies which is not supported in the Xamarin.iOS .NET API profile.
Source: https://docs.microsoft.com/en-us/xamarin/ios/internals/limitations#net-api-limitations

Lastly, there is an open GitHub issue on the scenario you are experiencing located here: https://github.com/dotnet/SqlClient/issues/861
While the scenario on iOS through Xamarin.Forms is deemed unsupported, the SQLClient side is investigating possible workarounds and further insight onto why the SNI assembly is being called on non-Windows platforms. Any updates from them on this scenario will likely be posted on that issue post - please feel free to interact with the post yourself as well to gather attention to it.

Thank you,
Anthony

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, Anthony,

I appreciate all your research and thoughtful response. I accept your answer, but I am disappointed. I feel that a direct connection to a SQL database from Xamarin.Forms should be supported for the specific case I am interested in: connecting to a local network SQL Server database over a WiFi connection.

The agricultural client I am working with has a limited IT budget and IT resources. Asking them to support an additional component (web server) on their internal network may not go over well. But I will ask if they will be willing to set up IIS on the same server that hosts the database.

Thank you again,
Kurt

0 Votes 0 ·
JessieZhang-2116 avatar image
0 Votes"
JessieZhang-2116 answered KurtK-5456 commented

Hello,​

Welcome to our Microsoft Q&A platform!

Entity Framework Core (EF Core) is a lightweight, extensible, cross-platform version of the Entity Framework. It’s slimmed down from the full Entity Framework and .NET Standard compatible.

For more details, I think you can first read through the article Entity Framework Core with Xamarin.Forms

and the official document : https://docs.microsoft.com/en-us/ef/core/

Note: EF Core 5.0 will not run on .NET Standard 2.0 platforms, including .NET Framework.

For more, you can check: https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core-ef-core-5-rc2/

Best Regards,

Jessie Zhang



If the response is helpful, please click "Accept Answer" and upvote it.


Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jessie, thank you for your answer. The documentation mentions SQLite as the database. That will not work for me. I need to connect to a SqlServer database on a local network.

For that reason I need to use the microsoft.entityframeworkcore.sqlserver package. Will that package work with xamarin.forms? (I was trying with the v3.1.9 version of microsoft.entityframeworkcore.sqlserver, using xamarin.forms 4.8 with .NET Standard 2.0.)

Thank you, regards,
Kurt

0 Votes 0 ·

Will that package work with xamarin.forms?

Yes, you can install this nuget by right clicking your app and selecting option Manage Nuget Packages for solution .

44940-image.png


0 Votes 0 ·
image.png (25.4 KiB)

Jessie, thank you again. However, I have already installed it and I get an error when trying to run the iphone emulator for testing. I showed that error in my original question. That is why I was asking whether it is actually supported with xamarin.forms.

I also tried deploying to my actual phone for testing. In that case I get a build error: "Could not AOT the assembly .../obj/iPhone/Debug/mtouch-cache/3-Build/System.Configuration.dll'".

Note that I also tried to run the same d/b access test using a console project using .NET Core 3.1 and the microsoft.entityframeworkcore.sqlserver package, and it ran fine.

The problem seems to be specific to using microsoft.entityframeworkcore.sqlserver package with a xamarin.forms project.

Thank you,
Kurt

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered KurtK-5456 commented

Hello,

A better idea is to not connect directly to the database but instead use restful api that works with json data that can be deserialized to a instance of a class/model. The Xamarin platform ships with support for JSON out of the box.

Here is a good place to start learning how to interact via rest service/api
https://docs.microsoft.com/en-us/xamarin/cross-platform/data-cloud/web-services/

Bottom line is to do EF work on a server, not on a cross platform project so the EF work is alway from the client side code.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Karen, thank you for your reply. I agree with your advice in general. However, my app is a special case in that it will access a database on a private local network. This is not an app that will be published to "the world".

For that reason I thought it would be reasonable to use direct sql access. (Also my existing Windows client applications use that approach.) I would be willing to employ the architecture you suggest. But since that will involve significant extra work and complexity, I first simply want to know if in fact the microsoft.entityframeworkcore.sqlserver package will work / is supported in a xamarin.forms application.

Thank you,
Kurt

0 Votes 0 ·

@KurtK-5456

Have you tried installing this package.


This is asked because of System.DllNotFoundException: sni.dll assembly which uses internal packages below which are not meant for direct usage.

https://www.nuget.org/packages/runtime.win-x64.runtime.native.System.Data.SqlClient.sni/
https://www.nuget.org/packages/runtime.win-x86.runtime.native.System.Data.SqlClient.sni/



0 Votes 0 ·

Thanks for the suggestion. No, I have not done that yet.

I looked in my Xamarin.Forms shared project dependencies. I saw the SqlServer package there. Under it was also:

Microsoft.Data.SqlClient (similar to the System.Data.SqlClient that you suggested?)

and under that:

runtime.native.system.data.sqlclient.sni. This should take care of sni.dll?

A bigger problem I have: When trying a test deployment to my phone, I get a build error (not a runtime crash):

Could not AOT the assembly .../System.Configuration.dll. Does that mean that Apple does not allow that assembly?

Thank you,
Kurt

0 Votes 0 ·
deckelmouck avatar image
0 Votes"
deckelmouck answered KurtK-5456 commented

hello,

had some similar issues. i wanted to connect directly to sqlserver within xamarin forms. it is no advise and on internet you should use a webservice for security reasons.

here is my solution:

best-practice-connecting-directly-to-sqlserver.html

perhaps you can use this information and get a solution you'll need for your purpose.

Regards

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thank you for your input! I was wanting to use Entity Framework Core rather than ADO.NET directly. Also, my first target for the Xamarin.Forms app is iOS. However, if I can in fact connect using ADO.NET, it may be OK. The big question is whether I will still get the "Could not AOT the assembly .../System.Configuration.dll" build error. I will give it a try and reply back here in a week or so.

Thank you, regards,
Kurt

0 Votes 0 ·

Hello,

I finally tried connecting to a local network SQL Server database over Wi-Fi using ADO.NET on an iPhone. It worked fine!

The only additional NuGet package I needed to install was System.Data.SqlClient. I used version 4.8.2, the latest stable version at the time. I was also using Xamarin.Forms v4.8.0.1687 and NETStandard.Library v2.0.3.

Since using EF Core for SQL Server is not currently supported in Xamarin.Forms, I will probably use ADO.NET, at least for now.

Regards,
Kurt

0 Votes 0 ·