在 LINQ 中使用 SQL 不同函数

本文关键字：函数 SQL LINQ | 更新日期: 2023-09-27 18:33:10

我想创建一个列表（MyList），其中我只得到每个IDNumber和SQL表中的人名（TABLE01）的一行。这是如何在 LINQ 中编写的。使用下面的代码，我得到"不支持指定方法"-

var MyList = (from a in TABLE01
              where a.IDNumber != " "
              select new
              {
                  Num = a.IDNumber.Distinct(),
                  Name = a.PersonName
              }).ToList();

**乔恩的完整堆栈跟踪： 

    at     Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e) at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(CompiledQuery compiledQuery, Object[] parentArgs, Object[] userArgs, Object lastResult)    
at Devart.Data.Linq.Provider.DataProvider.ExecuteAllQueries(CompiledQuery compiledQuery, Object[] userArguments)
   at Devart.Data.Linq.Provider.DataProvider.CompiledQuery.Devart.Data.Linq.Provider.ICompiledQuery.Execute(IProvider provider, Object[] userArgs)
  at Devart.Data.Linq.DataQuery`1.i()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Form1.Create_Modelist() in D:'projects'Form1.cs:line 488
at Form1.Form1_Load(Object sender, EventArgs e) in D:'projects'Form1.cs:line 565
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at .Program.Main() in D:'projects'Program.cs:line 18
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

在 LINQ 中使用 SQL 不同函数

我怀疑问题是它把IDNumber当作一个IEnumerable<char>（因为string确实是一个字符序列）。您可能想要更像以下内容：

var list = (from a in TABLE01
            where a.IDNumber != " "
            group a.Name by a.IDNumber into g
            select new { Num = g.Key, Name = g.First() }).ToList();

这基本上是按 IDNumber 分组的（这样你每个号码只会得到一个组），然后从每个组中获取"第一个"（即一些任意的）名称。

如果您希望每个ID都有一个名称，请使用Jon的答案。 如果您希望将不同的 ID/名称放在一起，请在列表中放置不同的 ID/名称，而不是单个属性。

var MyList = (from a in TABLE01
                    where a.IDNumber != " "
                    select new
                               {
                                   Num = a.IDNumber,
                                   Name = a.PersonName
                               }).Distinct().ToList();
相关文章：