C# LINQ query to data from multiple tables

Question

I have 2 tables:

Metrics (id, Metric) :[{1,Metric1},{2,Metric2},{3,Metric3}]

id | Metric
------------------------------------------------------------
1  |  Metric1
2  |  Metric2
3  |  Metric3

MetricsValuePerPerson (id, PersonId, Metricid, Value)

id | PersonId | Metricid | Value
------------------------------------------------------------
1  |  1       |     1    |  P1metric1Value
2  |  1       |     2    |  P1Metric2Value
3  |  1       |     3    |  P1metric3Value
4  |  2       |     1    |  P2Metric1Value
5  |  2       |     2    |  P2metric2Value
6  |  2       |     3    |  P2Metric3Value

I want to show the values in a grid where each row is a Person and each column is a Metric:

PersonId | Metric1         | Metric2        | Metric3
------------------------------------------------------------
P1       |  P1metric1Value | P1Metric2Value | P1Metric3Value
P2       |  P2metric1Value | P2Metric2Value | P2Metric3Value

Here is how I try to load the store:

var metricValues = from mv in dc.MetricsValuePerPerson
                   join m in dc.Metrics on mv.Metricid equals m.id
                   select new { Personid = mv.Personid, Metric1 = (codesnippet) , Metric2 = (codesnippet), Metric3 = (codesnippet) };

How can I replace (codesnippet) to get the Value of Metricx?

'each row is a Person and each column is a Metric' - Is it means, your First Column is Person and rest of the columns are Metrics? Is there only 3 metrics? — SelvaS, Jul 03 '18 at 10:23
@SelvaTS yes for the columns, there are multiple metrics not just 3 but are known — aggicd, Jul 03 '18 at 10:24

Cetin Basoz · Answer 1 · 2018-07-03T11:02:06.120

0

In LINQ, provided you have established relations in the database, you very rarely need to use JOIN keyword. In your case, within the DB you have relation between MetricsValuesPerPerson and Metrics, right? If so then:

var metricValues = from mv in dc.MetricsValuePerPerson
                   select new { 
                    Personid = mv.Personid, 
                    Metric1 = mv.Metric.Metric };

The result doesn't seem to be meaningful though (probably you would want to add Value too).

EDIT: This would be like a pivot, assuming you only have 3 metrics and at most 1 from each type of metric per person (ie: there is no personId, metricId, value like (1,1,10) and (1,1,20).

var result = from mv in MetricsValuePerPersons.Where(mvpp => mvpp.Metricid == 1)
                 let mv2 = MetricsValuePerPersons.SingleOrDefault(mvpp => mvpp.PersonId == mv.PersonId && mvpp.Metricid == 2)
                 let mv3 = MetricsValuePerPersons.SingleOrDefault(mvpp => mvpp.PersonId == mv.PersonId && mvpp.Metricid == 3)
                 select new {
        PersonId = mv.PersonId,
        Metric1 = mv.Value,
        Metric2 = mv2.Value,
        Metric3 = mv3.Value,
    };

edited Jul 03 '18 at 11:02

answered Jul 03 '18 at 10:27

Cetin Basoz

22,495
3
31
39

indeed, if you see the last line of my post I need the value of the metric – aggicd Jul 03 '18 at 10:31
@aggicd, Value of metric is in the other table and doesn't need a join. – Cetin Basoz Jul 03 '18 at 10:35
I need the name of the metric which is stored in `Metrics` table – aggicd Jul 03 '18 at 10:36
@aggicd, Would you please supply sample input data and how the output looks like. – Cetin Basoz Jul 03 '18 at 10:37
Please check original post – aggicd Jul 03 '18 at 10:44
1

@aggicd, Oh now I see you are after something like "pivot". IMHO it is not easy with LINQ and I would do that in backend. If your metrics are constantly 3 (like an enum) then you can do something like in the edited post. – Cetin Basoz Jul 03 '18 at 11:00

score 0 · Answer 2 · answered Jul 03 '18 at 11:29

You can do this by using GroupJoin (equivalent to LEFT JOIN in SQL). Join the MetricsValuePerPerson with Metrics and then GroupBy by the PersonId then Select the result. The sample code is in Lambda Join, but you can convert this to LINQ.

Working sample : C# Fiddle

var result = metricValuePerPerson.GroupJoin(metrics, mv => new { Id = mv.PersonId }, m => new { Id = m.Id }, 
        (mv, m) => new { mv = mv, m = m })
        .SelectMany(x => x.m.DefaultIfEmpty(), (x, y) => new {mv = x.mv, m = y })
        .GroupBy(g => g.mv.PersonId)
        .Select(x =>  
        {
           var grouped = x.ToList();
           return new 
            {
                PersonId = x.Key, //The below are sample code, please use your actual code.
                Metric1 = grouped[0].mv.PersonId + "|" + grouped[0].m.Metric + "|" + grouped[0].mv.Value,
                Metric2 = grouped[1].mv.PersonId + "|" + grouped[1].m.Metric + "|" + grouped[1].mv.Value,
                Metric3 = grouped[2].mv.PersonId + "|" + grouped[2].m.Metric + "|" + grouped[2].mv.Value,
            };
        })
        .ToList();

I get this error : A lambda expression with a statement body cannot be converted to an expression tree in the `.Select(x => — aggicd, Jul 03 '18 at 12:01
@aggicd I think you are directly using `IQueryable` object. You have to convert it into `List` before Join. Refer this answer https://stackoverflow.com/a/5179417/311255 — SelvaS, Jul 03 '18 at 12:30

C# LINQ query to data from multiple tables

2 Answers2