43945

How to create a dictionary in LINQ when populating a class?

Question:

Description:

At the moment I have a class which looks like so:

public class SupplierSummaryReport { public string SupplierName { get; set; } public Dictionary<string, decimal> DateValues { get; set; } }

Using LINQ, I am trying to create an IQueryable list of the SupplierSummaryReport class. However, when trying to create the dictionary in C#, the application fails. I'm not sure how to create the dictionary, can anyone help?

LINQ/C#:

public IQueryable<APData.Audit.Models.ReportModels.SupplierSummaryReport> GetSupplierSummaryReportData() { var data = (from i in _ctx.Invoices where i.Turnover == true select new { Year = i.InvoiceDate.Year.ToString(), AccountName = i.AccountName, Value = i.NetAmount_Home ?? 0 }); return data.GroupBy(r => new { r.Year, r.AccountName }) .Select(g => new APData.Audit.Models.ReportModels.SupplierSummaryReport { SupplierName = g.Key.AccountName, //I believe it fails when creating the dictionary DateValues = new Dictionary<string, decimal> { {g.Key.Year, g.Sum(r=> r.Value)} } }).OrderBy(r => r.SupplierName); }

Expected Outcome:

Supplier Name = "Test", DateValues = {2010, 500} Supplier Name = "Test2", DateValues = {2011, 900}

Actual Outcome:

Receive this error:

<blockquote>

Only list initializer items with a single element are supported in LINQ to Entities.

</blockquote>

Answer1:

Once you start the .Select(g => there's no point creating a dictionary as you have only one g.Key. You would only be creating a dictionary with one element in it if you tried. I think you have your logic wrong.

I think this is what your return statement should look like:

return data .GroupBy(r => r.AccountName) .Select(g => new APData.Audit.Models.ReportModels.SupplierSummaryReport { SupplierName = g.Key.AccountName, //I believe it fails when creating the dictionary DateValues = g .GroupBy(x => x.Year, x => x.Value) .ToDictionary(x => x.Key, x => x.Sum()) }) .OrderBy(r => r.SupplierName);

Now you may need to put a .ToArray() after data to bring the records in to memory to make this query work.

Recommend

  • Is NetworkStream Secure?
  • If error happens while creating VM on Azure, where would I find the error log
  • MobileFirst adapter is not deploying
  • JPA Hibernate intermediary table - resubmitted
  • Order documents by most popular in sharepoint content query webpart
  • Add Text & Excel table as picture to email
  • Pull any machine specific information from Windows audit log?
  • IdGeneratorStrategy unique for each kind
  • Proper way to fix potential security vulnerability in a dependency defined in package-lock.json
  • How do I get the delta for user_id since the previous months using oracle sql
  • Getting the SPWeb for a SPAuditEntry
  • Logstash not reading in new entries from MySQL
  • How do I efficiently query versioned rows/entities in PostgreSQL?
  • Data Factory - append fields to JSON sink
  • how to get OneToMany relationship revision in Hibernate envers
  • SQL Server : find last time user was connected
  • Filter Values of Current Week with XQuery
  • CAS: Unable to validate ProxyTicketValidator
  • Why are symfony DOMCrawler objects not properly passed between dependent phpunit tests?
  • How to monitor transaction isolation level changes in SQL Profiler or in any other tool
  • Symfony2 redirect to https route fails (uses wrong port)
  • ASPNetCore MVC Routing Let Server Handle Specific Route
  • Django return user model id with L
  • Django model for a Postgres view
  • npm 5.4.1 install/uninstall all failing
  • Combining two different ActiveRecord collections into one
  • OpenGL 3.3 on Mac OSX El Capitan with LWJGL
  • Spring Data JPA custom method causing PropertyReferenceException
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • C# - Serializing and deserializing static member
  • Sending data from AppleScript to FileMaker records
  • Running a C# exe file
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • How to delete a row from a dynamic generate table using jquery?
  • How to include full .NET prerequisite for Wix Burn installer
  • KeystoneJS: Relationships in Admin UI not updating
  • using HTMLImports.whenReady not working in chrome
  • Authorize attributes not working in MVC 4
  • Is it possible to post an object from jquery to bottle.py?
  • EntityFramework adding new object to nested object collection