My name
is
Jon Skeet

Using SQL distinct function in LINQ

I want to create a list (MyList), where I get only one row for each IDNumber and the PersonName from an SQL-table (TABLE01). How is this written in LINQ. With the code below I get "Specified method is not supported"-

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

**FULL STACK TRACE for Jon:

    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()

I suspect the problem is that it's treating IDNumber as an IEnumerable<char> (because string is indeed a sequence of characters). You probably want something more like:

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();

This basically groups by IDNumber (so that you'll only get one group per number) and then takes the "first" (i.e. some arbitrary) name from the each group.

See more on this question at Stackoverflow