Mauritz Swanepoel Mauritz Swanepoel - 3 months ago 8
C# Question

Return a multidimensional associative object from linq2entities

Disclaimer: very first c# project

I'm working on an export script which needs to export data from a sql database to csv. I am trying to get linq2entities to do most of the grunt work so that I can avoid unneccesary loops.

I would like to however seperate my linq2sql response into 2 objects (or a sub object refer to below as "calcs") of which the sub contents needs to be able to provide me with a key / value pair as I will need the key name later in my calculations to assign the output to a mapped key from another table.

I have tried a couple of iterations but cannot for the below working. The syntax appears correct but as it is now (below) I cannot even get a count (Any), if I just assign it as a normal list I get a rowcount but I am not able to access the sub object within the 2nd foreach loop.

So is it possible to return a mixed "sub object" from linq2sql and if not, what is my options?

public ActionResult Process()
{
// Getting the data:
using (printmanEntities db = new printmanEntities())
{

var result = (
from u in db.CustomerUsages
join c in db.Customers on u.CustomerId equals c.Id
join e in db.Centers on u.CenterId equals e.id
where u.Status == 1
group u by c.Code into gr
select new
{
CustomerId = gr.Key,
CenterCode = gr.FirstOrDefault().Center.CenterCode,
CustomerCode = gr.FirstOrDefault().Customer.Code,
// Is the below possible?
calcs = new List<KeyValuePair<string, int>>
{
new KeyValuePair<string,int>("TPrintBw",gr.Sum(u => u.TPrintBw)) ,
new KeyValuePair<string, int>("TPrintCol", gr.Sum(u => u.TPrintCol)),
new KeyValuePair<string, int>("TCopyBw", gr.Sum(u => u.TCopyBw)),
new KeyValuePair<string, int>("TCopyCol", gr.Sum(u => u.TCopyCol)),
new KeyValuePair<string, int>("TScan", gr.Sum(u => u.TScan)),
new KeyValuePair<string, int>("TFaxBw", gr.Sum(u => u.TFaxBw))
}
});

if (result.Any())
{

AllCodes = db.Codes.ToList();

dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("CenterCode", typeof(String));
dt.Columns.Add("BLANK", typeof(String));
dt.Columns.Add("CustomerCode", typeof(string));
dt.Columns.Add("ServiceCode", typeof(string));
dt.Columns.Add("Qty", typeof(Int32));

foreach (var v in result)
{
// I need to iterate through the items in the subobject here:
foreach (var i in v.calcs)
{
if (i.Value > 0)
{
DataRow dr = dt.NewRow();
dr["Date"] = DateTime.Now;
dr["CenterCode"] = v.CenterCode;
dr["BLANK"] = "";
dr["CustomerCode"] = v.CustomerCode;
dr["ServiceCode"] = GetServiceCode(i.Key);
dr["Qty"] = i.Value;
dt.Rows.Add(dr);
}

}
}

StringBuilder sb = new StringBuilder();

IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(",", fields));
}
string filename = DateTime.Now.ToString("yyyyddMM") + ".csv";
System.IO.File.WriteAllText(HttpContext.Server.MapPath(@"/csv/exports/" + filename), sb.ToString());

}
}

return View();
}

Answer

The problem with your query is that LINQ to Entities requires projecting to types with parameterless constructors and property setters, and KeyValuePair (as well as Tuple) does not meet that criteria and cannot be used.

One way would be to create and project to your own type, similar to KeyValuePair but compatible with EF:

public class KeyValuePairDto<TKey, TValue>
{
    public TKey Key { get; set; }
    public TValue Value { get; set; }
}

and then:

calcs = new List<KeyValuePairDto<string, int>>
{
    new KeyValuePairDto<string, int> { Key = "TPrintBw", Value = gr.Sum(u => u.TPrintBw) },
    new KeyValuePairDto<string, int> { Key = "TPrintCol", Value = gr.Sum(u => u.TPrintCol) },
    new KeyValuePairDto<string, int> { Key = "TCopyBw", Value = gr.Sum(u => u.TCopyBw) },
    new KeyValuePairDto<string, int> { Key = "TCopyCol", Value = gr.Sum(u => u.TCopyCol) },
    new KeyValuePairDto<string, int> { Key = "TScan", Value = gr.Sum(u => u.TScan) },
    new KeyValuePairDto<string, int> { Key = "TFaxBw", Value = gr.Sum(u => u.TFaxBw) },
}

Another way is to project to list of anonymous type (via array initializer combined with ToList), which is supported:

calcs = new []
{
    new { Key = "TPrintBw", Value = gr.Sum(u => u.TPrintBw) },
    new { Key = "TPrintCol", Value = gr.Sum(u => u.TPrintCol) },
    new { Key = "TCopyBw", Value = gr.Sum(u => u.TCopyBw) },
    new { Key = "TCopyCol", Value = gr.Sum(u => u.TCopyCol) },
    new { Key = "TScan", Value = gr.Sum(u => u.TScan) },
    new { Key = "TFaxBw", Value = gr.Sum(u => u.TFaxBw) },
}.ToList()