在Powershell Cmdlet中使用实体框架核心吗？ [英] Using Entity Framework Core in a Powershell Cmdlet?

问题描述

是否可以构建包含实体框架（Core或EF6）的PowerShell cmdlet来访问SQL Server数据库？

我一直在编写cmdlet C＃已经好几年了，但是由于似乎是程序集版本冲突，我在最近的coupla日子里经历了一场噩梦，试图在项目中使用Entity Framework。

Project具有三个库：

• MyProject.Commands.dll -要加载的cmdlet


• MyProject.Lib.dll -cmdlet使用的通用库代码


• MyProject.EF.dll -仅用于实体框架。

EF是独立的，因为我使用数据库优先的方法（我必须对现有系统进行逆向工程），因此从数据库构建的各种 .cs 模型文件都放在一个单独的单元中，因此请保持它们的一致性

我正在Windows 10上构建所有这些项目，并且该项目仅需要在 this machin上运行e 。

第一次尝试：VS 2017 / .Net Framework 4.7.1

使用nuget添加所需的软件包后，一切都构建良好，但是在 MyProject.Commands.dll Import-Module $ c>让我：

  System.IO.FileNotFoundException：无法加载文件或程序集\ 
'System .ComponentModel.Annotations，版本= 4.2.0.0，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a'\ 
或其依赖项之一。该系统找不到指定的文件。 
  

打开某种绑定调试日志后，

  ===预绑定状态信息=== 
日志：DisplayName = System.ComponentModel.Annotations，版本= 4.2.0.0，Culture =中性，PublicKeyToken = b03f5f7f11d50a3a 
（完全指定）
 LOG：Appbase = file：/// C：/Windows/System32/WindowsPowerShell/v1.0/ 
 LOG：初始PrivatePath = NULL 
调用程序集：Microsoft.EntityFrameworkCore.Relational，版本= 3.1.0.0，区域性=中性，PublicKeyToken = adb9793829ddae60。 
  

我从来没有找到 System.ComponentModel的确切版本.Annotation ，但互连网中充斥着同一问题的报告。我用DLL中的AssemblyResolver尝试了各种方法，各种框架的不同版本最终无济于事。

最终我找到了此页面> https://docs.microsoft.com/en-us/ef/core/platforms/ 这表明任何.NET Framework版本（在.NET Core上）均不支持EF Core 3.x，但是几天前的这篇博客文章 https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core- 3-1-and-entity-framework-6-4 / 表示它现在已受支持。

上面的内容很粗略，因为它来自

B计划：.NET Core 3.1上的EF Core

确定EF Core可能是一个赌注无论如何，为了未来的发展方向，我得到了VS 2019，并将整个项目重新定位到.NET Core 3.1上的EF Core 3.1。这是我对.NET Core的首次尝试。

花了一些时间才能整理出依赖项（包括换出 System.Management.Automation 表示 Microsoft.PowerShell.SDK ），但最终全部完成了。安装Powershell 6之后，我现在受到欢迎：

  VERBOSE：从路径'D：\Dev\加载模块MyProject\MyProject.Commands\bin\Debug\netcoreapp3.1\MyProject.Commands.dll。 
 Import-Module：无法加载文件或程序集 System.Runtime，版本= 4.2.2.0，区域性=中性，PublicKeyToken = b03f5f 
 7f11d50a3a。该系统找不到指定的文件。 
  

棒极了！

现在找不到 System.Runtime 的4.2.2.0版本，我真的很茫然。

在每个项目的生成区域 bin\Debug\netcoreapp3.1\ 目录中是一个 *。deps.json 文件，其中列出了各种依赖关系，但对 System.Runtime 的每个引用均适用于4.3.0版。

我尝试了Process Monitor，这是用于查看装配体的各种工具，可在GAC周围闲逛。 Nuffin。

将其制作为应用

将库保持不变，我将主要逻辑封装到控制台应用程序中并将其编译为 .EXE 。

它是第一次工作。

这可能吗？

构建控制台应用程序的工作如此顺利的事实表明，尝试在PowerShell中进行所有这些工作可能会很好

一个独立的应用程序可以控制其自身的依赖关系，但是构建必须托管在另一个程序（powershell）中的DLL意味着我们必须工作并发挥出色

让我惊讶的是，可能可以仔细分析现有的 pwsh.exe （即Powershell 6）并找到其确切的依存关系集，然后寻找恰好与之匹配的EF Core版本。

作为数据点，此代码的先前版本在通过直接SQL调用与SQL Server进行通信时可以很好地工作。 ，因此所有数据库访问负担还不足以破坏cmdlet兼容性。但是添加EF内核似乎可以。

我讨厌我的生活。我注定要失败吗？

编辑要清楚一点，我不是要在PowerShell 脚本中使用实体框架，我不确定自己是否想要。它在C＃DLL中使用EF，可以通过编程方式完成与数据库的所有繁重通话。

我已经有了相当合理的直接SQL调用（同样在C＃中）很好，但是我有几十个表可以进行逆向工程，让EF通过构建模型和所有易于访问的代码为我做这件事非常有用。

  foreach（DbContext.Employee 
中的变量项，其中（x => x.Salary> 1000.00）
 .OrderBy（x => x.EmployeeId ））
 {
做某事
} 
  

实体框架程序包根据数据库中的定义构建了 Employee 类（尽管您也可以使用代码优先方法），而上面的内容实际上是我必须编写的唯一代码。 / p>

我很长时间以来一直想在PowerShell cmdlet中使用EF，但从未使其能够正常工作。

关于实体的不错的开车兜风框架： https://docs.microsoft.com/en-us/ef/core /

编辑今天晚上，我发现我在为.NET Core而不是.NET Standard（其中与原始的.NET Framework再次不同），这解释了很多事情为何无法正确加载的原因。

.NET Core似乎是可移植的子集，并且我只是因为不知道有什么不同而迷失了自己。另外： Core似乎已超载（例如， Entity Framework Core可以在 .Net Standard上正常运行）。 gh。

我仍然无法完全完成此任务并不能像cmdlet一样玩，但是从好的方面来说，我确实设法多烧了一些小时。

很可能对此没有好的解决方法。

编辑（6 / 2020）今天早上我发现六个月前打开的程序集绑定日志记录将我的硬盘驱动器填满了 C：\WINDOWS\SysWOW64\config\systemprofile\AppData \Local\Microsoft\Windows\INetCache\IE 目录，其中包含千兆字节的HTM文件。糟糕！

这篇文章包含有关如何通过注册表启用/禁用它的说明。

解决方案

I做了一些试验，实际上使它可以在Powershell 7中工作，但是我无法以代码优先的方式将它与Powershell类一起使用，因为Powershell类的创建方式与dbset不兼容。但是，DB-first确实可以工作。

Is it possible to build a PowerShell cmdlet that incorporate Entity Framework (Core or EF6) for access to an SQL Server database?

I've been writing cmdlets in C# for years, but I've gone through a nightmare the last coupla days trying to use Entity Framework in a project due to what appear to be assembly version clashes.

Project has three libraries:

• MyProject.Commands.dll - cmdlets to load
• MyProject.Lib.dll - common library code used by the cmdlets
• MyProject.EF.dll - the Entity Framework stuff only.

EF is separate because I use a database-first approach (I have to reverse engineer an existing system), so the various .cs model files built from the database are in a separate unit just so keep them straight.

I'm building all this on Windows 10, and the project only needs to run on this machine. It's talking to an SQL Database in the Azure cloud.

First try: VS 2017 / .Net Framework 4.7.1

After using nuget to add the required packages, everything built fine, but using Import-Module on MyProject.Commands.dll gets me:

System.IO.FileNotFoundException: Could not load file or assembly \
   'System.ComponentModel.Annotations, Version=4.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' \
   or one of its dependencies. The system cannot find the file specified.

After turning on some kind of binding debug logging,

=== Pre-bind state information ===
LOG: DisplayName = System.ComponentModel.Annotations, Version=4.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
 (Fully-specified)
LOG: Appbase = file:///C:/Windows/System32/WindowsPowerShell/v1.0/
LOG: Initial PrivatePath = NULL
Calling assembly : Microsoft.EntityFrameworkCore.Relational, Version=3.1.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60.

I've never been able to find that exact version of System.ComponentModel.Annotation, but the interwebs are full of reports of this same issue. I tried all kinds of things with the AssemblyResolver in the DLL, different versions of various frameworks, ultimately got nowhere.

Eventually I found this page https://docs.microsoft.com/en-us/ef/core/platforms/ that suggests that EF Core 3.x is not supported on any .NET framework version (on .NET Core), but this blog post from a few days ago https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core-3-1-and-entity-framework-6-4/ suggests that it's now supported after all.

The above is a rough retelling because it's from a loooong time ago (late last night) before I went to plan B. This was maddening.

Plan B: EF Core on .NET Core 3.1

Deciding that EF Core is likely a better direction for the future anyway, I got VS 2019 and re-targeted my entire project to EF Core 3.1 on .NET Core 3.1. This is my first foray into .NET Core.

It took a little while to get dependencies sorted out (including swapping out System.Management.Automation for Microsoft.PowerShell.SDK), but eventually it all built. After installing Powershell 6, I'm now greeted with:

VERBOSE: Loading module from path 'D:\Dev\MyProject\MyProject.Commands\bin\Debug\netcoreapp3.1\MyProject.Commands.dll'.
Import-Module : Could not load file or assembly 'System.Runtime, Version=4.2.2.0, Culture=neutral, PublicKeyToken=b03f5f
7f11d50a3a'. The system cannot find the file specified.

Awesome!!

Now it's not able to find version 4.2.2.0 of System.Runtime, and I'm really at a loss.

Inside each project's build area bin\Debug\netcoreapp3.1\ directory is a *.deps.json file that lists all kinds of dependencies, but every single reference to System.Runtime is for ver 4.3.0.

I've tried Process Monitor, various tools for looking at assemblies, poking around the GAC. Nuffin.

Making it an App

Leaving the libraries unchanged, I encapsulated the main logic into a console application and compiled it down to a .EXE.

It worked the first time.

Is this even possible?

The fact that building the console app worked so smoothly suggests that trying to make all this work in PowerShell may well be unsolvable.

A standalone app is in control of its own dependencies, but building DLLs that have to host inside another program (powershell) means we have to work and play well with its dependencies.

It strikes me that it might be possible to carefully analyze the existing pwsh.exe (that's Powershell 6) and find its exact set of dependencies, then look for a version of EF Core that just happens to match that set. This does not seem promising, but would likely be brittle.

As a data point, a previous version of this code worked fine when talking to the SQL server via direct SQL calls, so all the database-access baggage wasn't enough to break cmdlet compat. But adding EF core seems to.

I hate my life. Am I doomed?

EDIT Just to be clear, I'm not trying to use Entity Framework in a PowerShell script, I'm not sure I'd even want to. It's using EF in a C# DLL that does all the heavy lifting of talking to the database programatically

I already have fairly reasonable direct SQL calls (again, in C#) that works pretty well, but I have dozens of tables to reverse engineer, and it's amazingly helpful to have EF do this for me by building the models and all the easy-access code.

    foreach (var item in DbContext.Employee
             .Where(x => x.Salary > 1000.00)
             .OrderBy(x => x.EmployeeId))
    {
       do something
    }

The Entity Framework package built the Employee class from the definition in the database (though you can also use code-first approaches), and the above is literally the only code I had to write.

I've wanted to use EF in a PowerShell cmdlet for a long time but have never been able to make it work.

A nice drive-by read on Entity Framework: https://docs.microsoft.com/en-us/ef/core/

EDIT This evening I found that I had been building my code for .NET Core rather than .NET Standard (which is yet again different from the original .NET Framework), and that explains much of why things were not loading properly.

.NET Core seems to be a portable subset, and I just got lost in the weeds from things that I didn't know were different. Also: "Core" seems to be overloaded (e.g. "Entity Framework Core" can run just fine on ".Net Standard"). Ugh.

I still haven't been able to get this to all work and play well as a cmdlet, but on the plus side I did manage to burn a few more hours.

Likely there's no good solution to this.

EDIT (6/2020) I found out this morning that the assembly binding logging I turned on six months ago was filling up my hard drive in the C:\WINDOWS\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\INetCache\IE directory with gigabytes of HTM files. Oops.

This post includes instructions on how to enable/disable this via the registry.

解决方案

I did some experimenting with this and actually got it to work in Powershell 7, however I couldn't get it to work with Powershell Classes as a code-first approach as powershell classes get created in a way that doesn't gel with dbset. DB-first did work however.

这篇关于在Powershell Cmdlet中使用实体框架核心吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！