CODEWITHSUNDEEP
c#linqentity-frameworkiqueryable
Raphael
Raphael

Reputation: 1687

Entity Framework IQueryable, with 2 queries

I wanted to ask if there is a better way to achieve what I'm trying to do.

I need to get all the records of a child graph that have the date greter or equal to the given date, and the first record with date minor to the given date.

I found this solution that works but I'm not sure that this will be the best way.

var q = context.Istc0.Include("Interests").Where(a => a.IIsin == listKey).Select(a => new
{
    Istc0 = a,
    Interests = a.Interests.Where(d => d.InDat >= date)
});

var qq = context.Istc0.Include("Interests").Where(a => a.IIsin == listKey).Select(a => new
{
    Istc0 = a,
    Interests = a.Interests.Where(d => d.InDat < date).OrderByDescending(d => d.InDat).Take(1)
});

var xxx = q.ToList()[0].Istc0;
xxx = qq.ToList()[0].Istc0;

return xxx;

Upvotes: 0

Views: 413

Answers (2)

Raphael
Raphael

Reputation: 1687

Hi thanks for the answer.

With the query you posted I don't get all the data I need. I don't think that I can get what I need with one query.

Interest table
-----------------
2013-5-16 | 1%
2013-6-21 | 0.8%
2013-7-12 | 0.5%
2013-8-06 | 0.6%

The istc0 table contains all my isin number, and as a relationship with the interest table One to Many.

On of the parameter of the function is the date. so if I pass for example

date = 2013-7-01

the result I need is the following:

Interest table
-----------------
2013-6-21 | 0.8%
2013-7-12 | 0.5%
2013-8-06 | 0.6%

So in my forst post the first query assigned to q retrieves all the interests after july the first, the second query assigned to qq retrieves the first interest rate before july the first. My function does the job, and I get the set of data that I need, but it doesn't seem to be the cleanest way to do it.

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

I do not know which one you need to return. Probably yyy.

var q = context.Istc0.Include("Interests").Where(a => a.IIsin == listKey).Select(a => new
    {
        Istc0 = a,
        Interests = a.Interests.Where(d => d.InDat >= date)
    }).ToList();


var xxx = q[0].Istc0;
var yyy = q.OrderByDescending(d => d.InDat).Take(1).SingleOrDefault().Istc0;

Dictionary<string,decimal> result = new Dictionary<string,decimal>();

result.Add("all",xxx);
result.Add("previous",yyy);

return result;

In this example i create a Dictionary with a string key (could be an integer or whatever you like) and with a decimal(i am guessing the returned value is of decimal type) value to store the results of the queries. Then i return this dictionary.

Another way would be to have a strongly typed object and return a list of that object.

Lastly, you could define two output parameters (read this). For instance:

public void GetInterestRates(string listKey, out decimal currentRate, out decimal previousRate)
{
    var q = context.Istc0.Include("Interests").Where(a => a.IIsin == listKey).Select(a => new
        {
            Istc0 = a,
            Interests = a.Interests.Where(d => d.InDat >= date)
        }).ToList();


    var currentRate = q[0].Istc0;
    var previousRate = q.OrderByDescending(d => d.InDat).Take(1).SingleOrDefault().Istc0;

}

And when you want to use this:

decimal currentRate , previousRate;
GetInterestRates(listKey, currentRate , previousRate);

Upvotes: 1

Related Questions