Strongly typed linq group by

Question

Relevant Model;

public class category_trans
{
    [Key]
    [Column(Order = 1)]
    public int category_id { get; set; }
    [Key]
    [Column(Order = 2)]
    public int language_id { get; set; }
    public string name { get; set; }
}

present Linq query, working:

IQueryable<category_trans> APC =
            from ct in db.category_trans
            from c in db.Categories
            from l in db.ISO_Languages
            where (
            ct.category_id == c.ID 
            && ct.language_id == l.ID 
            && l.code.Substring(0,2) == culture
            && c.IsDeleted == false)
            select ct;

I would like to group query result in order to get distinct category_trans.name (now I am getting multiple ones).

Trying

IQueryable<category_trans> APC =
            from ct in db.category_trans
            from c in db.Categories
            from l in db.ISO_Languages
            where (
            ct.category_id == c.ID 
            && ct.language_id == l.ID 
            && l.code.Substring(0,2) == culture
            && c.IsDeleted == false)
            group ct by ct.name into g
            select new
            {
                category_id = g.category_id,
                name = g.name
            };

gives me errors on both g.category_id and g.name

IGrouping <string,category_trans> does not contain a definition for 'category_id'...

Why grouping seems to lose reference to model members and how may it be fixed?

Answer 1

Because a group can contain multiple, you can use the Key property for the name:

IQueryable<category_trans> APC =
        from ct in db.category_trans
        from c in db.Categories
        from l in db.ISO_Languages
        where (
        ct.category_id == c.ID 
        && ct.language_id == l.ID 
        && l.code.Substring(0,2) == culture
        && c.IsDeleted == false)
        group ct by ct.name into g
        select new
        {
            category_id = g.First().category_id,
            name = g.Key
        };

I have used First to get the first category_id, you might want to use a different logic.