CODEWITHSUNDEEP
c#linq
Jazzy
Jazzy

Reputation: 519

Linq to SQL select returns list within an anonymous type

The SQL that returns this result is below.

My linq to sql query returns an object, of concrete type "Credentials", that among other properties includes List(of string) GradesList. I have control over the Credentials type, and the query, but not the underlying database.

Here is what my return currently looks like:

Id  Code    IssueDate   ExpDate    Grd1 Grd2 Grd3 Grd4 Grd5 Grd6
6   TE0150  4/21/1999   4/21/2002   N    N    N    N    Y    Y
6   TE0150  3/11/2003   3/28/2007   N    N    N    N    Y    Y
6   TE0150  3/29/2012   3/28/2017   N    N    N    N    Y    Y
6   TE0140  5/3/2007    5/3/2008    Y    Y    Y    Y    N    N
6   TE0140  3/29/2012   3/28/2017   Y    Y    Y    Y    Y    Y

What I want is:

Id  Code    IssueDate   ExpDate    GradesList
6   TE0150  4/21/1999   4/21/2002   Grd5,Grd6 
6   TE0150  3/11/2003   3/28/2007   Grd5,Grd6
6   TE0150  3/29/2012   3/28/2017   Grd5,Grd6
6   TE0140  5/3/2007    5/3/2008    Grd1,Grd2,Grd3,Grd4
6   TE0140  3/29/2012   3/28/2017   Grd1,Grd2,Grd3,Grd4,Grd5,Grd6

Here is the SQL. The PPS_ENDORSE_DTL table has a column for each Grd level, with "Y" or "N" as a value.

from en     in PPS_ENDORSE_DTL
join jpi    in JPM_JP_ITEMS     on new { x1 = en.ACCOMPLISHMENT, x2 = en.DT_ISSUED }        equals new { x1 = jpi.JPM_CAT_ITEM_ID, x2 = jpi.EFFDT }
join jp     in JPM_PROFILE      on new { x1 = jpi.JPM_PROFILE_ID, x2 = en.EMPLID }          equals new { x1 = jp.JPM_PROFILE_ID, x2 = jp.EMPLID } 
join jci1   in JPM_CAT_ITEMS    on      en.PPS_ENDORSE_SUBJ                                 equals      jci1.JPM_CAT_ITEM_ID
join jci2   in JPM_CAT_ITEMS    on new { x1 = jpi.JPM_CAT_TYPE, x2 = jpi.JPM_CAT_ITEM_ID }  equals new { x1 = jci2.JPM_CAT_TYPE, x2 = jci2.JPM_CAT_ITEM_ID }
join es     in PPS_ENDORSE_SUB  on      jci1.JPM_CAT_ITEM_IDequals                          equals      es.PPS_ENDORSE_SUBJ

Upvotes: 0

Views: 67

Answers (1)

LB2
LB2

Reputation: 4860

To that big inline linq statment, add

.AsEnumerable().Select(r => {
    var selectedGrades = new List();
    if (r.Grd1 == "Y") selectedGrades.Add("Grd1")
    if (r.Grd2 == "Y") selectedGrades.Add("Grd2")
    ...
    return new TypeWithGradesList{
         ....
         GradesList = string.Join(",", selectedGrades.ToArray())
    }
})

The `TypeWithGradesList is a model that has all the fields of your final result.

Upvotes: 1

Related Questions