Can this data be retrieved with a single linq query?

Question

I'm using Entity Framework. The class below represents a table from the database.

public partial class TermBucket
{
    public short ID { get; set; }
    public byte Period { get; set; }
    public byte Min { get; set; }
    public byte Max { get; set; }
}

The PK is ID and Period, so there can be multiple ID's in the table.

When querying the entity, I have at my disposal the period + time (the number associated with the period). Period is set up as an enum, so I would use it as:

Time: 3
Period: 3 (Days)

What I want to do is to find the bucket that matches by requirements and then get all entries for that bucket. Here's what I currently do:

Step 1: Get the ID

return r.Find() // this is my repository
    .AsNoTracking()
    .Where(x => (int)tp.Period == x.Period && tp.Time >= x.Min && tp.Time <= x.Max)
    .Select(x => x.ID)
    .Single();

Step 2: Get all entries for the ID, using the retrieved ID

return r.Find()
    .AsNoTracking()
    .Where(x => x.ID == ID );

So, there are 2 distinct queries, but is it possible to retrieve this data in one go?

Maybe you can just nest the first query within the second? Have you tried something like the answers found here: http://stackoverflow.com/questions/5354050/nested-select-linq-query — MaxRev17, Dec 09 '14 at 11:20
Yes just combine the predicates using &&, `.Where(x => (int)tp.Period == x.Period && tp.Time >= x.Min && tp.Time <= x.Max && x.ID == ID )` — Ben Robinson, Dec 09 '14 at 11:23

score 0 · Answer 1 · answered Dec 09 '14 at 11:26

0

Can't you just combine them?

return r.Find()
    .AsNoTracking()
    .Where(x => x.ID == r.Find()
        .AsNoTracking()
        .Where(x => (int)tp.Period == x.Period && tp.Time >= x.Min && tp.Time <= x.Max)
        .Select(x => x.ID)
        .Single());

answered Dec 09 '14 at 11:26

Rufus L

36,127
5
30
43

Sorry for the naivity Rufus - Linq tend to baffle me. This looks like it might work - I'll give it a go. – dotnetnoob Dec 09 '14 at 11:38

score 0 · Answer 2 · answered Dec 09 '14 at 14:08

You can do it using join. Example

    public partial class TermBucket
    {
        public short ID { get; set; }
        public byte Period { get; set; }
        public byte Min { get; set; }
        public byte Max { get; set; }
    }

    static void Main(string[] args)
    {
        List<TermBucket> l = new List<TermBucket>();
        l.Add(new TermBucket() { ID = 1, Period = 3, Min = 10, Max = 14 });
        l.Add(new TermBucket() { ID = 1, Period = 4, Min = 10, Max = 13 });
        l.Add(new TermBucket() { ID = 1, Period = 5, Min = 100, Max = 25 });
        l.Add(new TermBucket() { ID = -1, Period = 3, Min = 10, Max = 12 });

        int period = 3;
        int minV = 10;
        int maxV = 13;
        var res = from e in l 
                  join e2 in l on e.ID equals e2.ID
                  where e.Period == period && minV >= e.Min && maxV <= e.Max 
                  select e2;

        foreach (var r in res)
        {
            Console.WriteLine(r.ID + " " + r.Period);
        }

        Console.ReadLine();
    }

Will output

1 3
1 4
1 5

score 0 · Answer 3 · answered Dec 09 '14 at 14:15

Yes, it is:

return r.Find()
    .AsNoTracking()
    .Where(x => x.ID == r.Find()
    .AsNoTracking()
    .Where(x => (int)tp.Period == x.Period && tp.Time >= x.Min && tp.Time <= x.Max)
    .Select(x => x.ID)
    .Single());

But I would recommend breaking it up into two queries, as you already have it, to handle the case where the first query returns no results. (As it is, currently, .Single() will throw an exception if .Select() is empty).

Can this data be retrieved with a single linq query?

3 Answers3