C#•7mo ago

EF/Linq select all columns of main table, but only some cols of joined table?

I find myself often wanting to select all of a table, but very limited columns of joined tables. Is it possible to do without explicitly listing all columns? Example:

public class GetStuff {
    public ThingOne GetThingOne() {
        var data = new List<ThingOne>() { /*...*/ };

        var thing = data.Select(x => new ThingOne {

            // I want all ThingOne properties. Can I select them all without having to explicitly mention each one? E.g like a "select ThingOne.*"
            A = x.A,
            B = x.B,
            C = x.C,
            D = x.D,
            Etc = x.Etc,
            MuchMore = x.MuchMore,

            //I only want 1 property in ThingTwo
            ThingTwo = new ThingTwo {
                A2 = x.ThingTwo.A2
            }
        }).FirstOrDefault();

        return thing;
    }
}

public class ThingOne {
    public ThingTwo ThingTwo { get; set; }
    public int A { get; set; }
    public int B { get; set; }
    public int C { get; set; }
    public int D { get; set; }
    public int Etc { get; set; }
    public int MuchMore { get; set; }
}

public class ThingTwo {
    public int A2 { get; set; }
    public int B2 { get; set; }
    public int C2 { get; set; }
    public int D2 { get; set; }
    public int Etc2 { get; set; }
}

public class GetStuff {
    public ThingOne GetThingOne() {
        var data = new List<ThingOne>() { /*...*/ };

        var thing = data.Select(x => new ThingOne {

            // I want all ThingOne properties. Can I select them all without having to explicitly mention each one? E.g like a "select ThingOne.*"
            A = x.A,
            B = x.B,
            C = x.C,
            D = x.D,
            Etc = x.Etc,
            MuchMore = x.MuchMore,

            //I only want 1 property in ThingTwo
            ThingTwo = new ThingTwo {
                A2 = x.ThingTwo.A2
            }
        }).FirstOrDefault();

        return thing;
    }
}

public class ThingOne {
    public ThingTwo ThingTwo { get; set; }
    public int A { get; set; }
    public int B { get; set; }
    public int C { get; set; }
    public int D { get; set; }
    public int Etc { get; set; }
    public int MuchMore { get; set; }
}

public class ThingTwo {
    public int A2 { get; set; }
    public int B2 { get; set; }
    public int C2 { get; set; }
    public int D2 { get; set; }
    public int Etc2 { get; set; }
}

20 Replies

Unknown User•7mo ago

Message Not Public

mejobloggs•7mo ago

.Include will get all, but it won't help the query only get A2 for ThingTwo

Unknown User•7mo ago

Message Not Public

mejobloggs•7mo ago

I gave it a try and you can't seem to do anything like that. I can only find how to do it by explicitly stating each individual column I want from ThingOne You can filter rows inside an Include, but not columns ooh hang on, i think i see what im doing wrong let me try again Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. yeah doesnt seem possible

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

i dont get what you're trying to do can you do?

db.Foos
.Select(d => new
{
  Bar = d.Bar,
  FooId = d.Id,
}).ToListAsync();

db.Foos
.Select(d => new
{
  Bar = d.Bar,
  FooId = d.Id,
}).ToListAsync();

where Bar is a nav prop? yes. other than that i dont understand what you're trying to achieve.

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

db.Foos
.Select(x => new
{
  x.Id,
  x.Bla
  Bar = new {
    x.Bar.Id,
    x.Bar.BarString
  },
  Kix = new {
    x.Kix.Id,
    x.Kix.KixInt,
    x.Kix.KixBool
  }
}).ToListAsync();

db.Foos
.Select(x => new
{
  x.Id,
  x.Bla
  Bar = new {
    x.Bar.Id,
    x.Bar.BarString
  },
  Kix = new {
    x.Kix.Id,
    x.Kix.KixInt,
    x.Kix.KixBool
  }
}).ToListAsync();

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

yes

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

db.Foos
.Select(x => new
{
  Entity = x,
  Bar = new {
    x.Bar.Id,
    x.Bar.BarString
  },
  Kix = new {
    x.Kix.Id,
    x.Kix.KixInt,
    x.Kix.KixBool
  }
}).ToListAsync();

db.Foos
.Select(x => new
{
  Entity = x,
  Bar = new {
    x.Bar.Id,
    x.Bar.BarString
  },
  Kix = new {
    x.Kix.Id,
    x.Kix.KixInt,
    x.Kix.KixBool
  }
}).ToListAsync();

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

im not sure EF would ever be able to support that where does the spread stop

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

which includes the nav props

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

side note: that will track x as an entity any changes you make will be committed back to the db if you save

Unknown User•7mo ago

Message Not Public

Patrick•7mo ago

just x

Gaming

Programming

EF/Linq select all columns of main table, but only some cols of joined table?