How To Process Entity Framework Context int Lambda Expression

Question

I using ef 6.13 to get some data, and return a new class data to my app.

public List<v_apply_detail2> GetApplyList()
{
    using (yoyoEntities ds = new yoyoEntities())
    {
        return  datas = ds.v_apply_detail.Select(p => new v_apply_detail2 { apply_id = p.apply_id, time_line = ds.yoyo_apply_timeline.ToList() }).ToList();
    }
}

It's runing normal.
and I want to move the Select method in a New Func, and using the dbcontext yoyoEntities(ds).

Func<v_apply_detail, v_apply_detail2> SelectOutApply(yoyoEntities ds, bool includetimeline = false)
{
    return (p => new v_apply_detail2
    {
        apply_id = p.apply_id,
        time_line = includetimeline ? ds.yoyo_apply_timeline.Where(x => x.apply_id == p.apply_id).OrderByDescending(x => x.time).ToList() : null
    });

}

public List<v_apply_detail2> GetApplyList()
{
    using (yoyoEntities ds = new yoyoEntities())
    {
        return  datas = ds.v_apply_detail.Select(SelectOutApply(ds,true)).ToList();
    }
}

When I call the GetApplyList function , it's get a error :Entity Framework: There is already an open DataReader associated with this Connection which must be closed first.

How do I can using the dbcontext in Func<> or Expression<> ? think guys!

Why don't you try passing the query rather than datacontext to the function? — Amanvir Mundra
– Amanvir Mundra, Commented Apr 11, 2017 at 8:00
Agree with @AmanvirSinghMundra, you are doing a Where in a Select at the "same time" for two different dataset. EF is trying to acceed database concurently — OrcusZ
– OrcusZ, Commented Apr 11, 2017 at 8:02
I need return Different data for Different request, and i have some function has same query ,so i need the common Select Func. — bofuqiang
– bofuqiang, Commented Apr 11, 2017 at 8:27

Amanvir Mundra · Accepted Answer · 2017-04-11 11:57:51Z

0

Allowing Multiple Active result sets should fix the error that you are seeing.

string connectionString = "Data Source=MSSQL1;" +   
"Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +  
"MultipleActiveResultSets=True";

https://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx

But I believe you should be passing a queryable rather than a dbcontext.

Update: Make the Timeline property Virtual.

public partial class v_apply_detail2 {
public long apply_id { get; set; }
public long hiring_id { get; set; }
public string hiring_title { get; set; }
public string publish_user_id { get; set; }
public string publish_company_id { get; set; }
public string user_id { get; set; }
public Nullable<System.DateTime> pre_accept_time { get; set; }
public sbyte join_type { get; set; }
public Nullable<decimal> deal_pay { get; set; }
public Nullable<System.DateTime> deal_time { get; set; }
public Nullable<System.DateTime> start_work_time { get; set; }
public Nullable<System.DateTime> finish_time { get; set; }
public string master_appraise { get; set; }
public string worker_appraise { get; set; }
public Nullable<int> master_rating { get; set; }
public Nullable<int> worker_rating { get; set; }
public int status { get; set; }
public Nullable<System.DateTime> master_rating_time { get; set; }
public Nullable<System.DateTime> worker_rating_time { get; set; }
public string hiring_snapshots { get; set; }
public virtual List<yoyo_apply_timeline> time_line { get; set; }
public member_info member_info { get; set; }
public company_info company_info { get; set; }}

Then instead of using context use the query variable:

Func<v_apply_detail, v_apply_detail2> SelectOutApply(yoyoEntities ds, bool includetimeline = false) {
return (p => new v_apply_detail2
{
    apply_id = p.apply_id,
    time_line = includetimeline ? p.timeline.OrderByDescending(x => x.time) : null
});}

Better way (single method): Just to keep things simple the below should work well (just don't use datacontext to query timelines again):

public List<apply_detail_dto> GetApplyList(){
using (yoyoEntities ds = new yoyoEntities())
{
    return  datas = ds.v_apply_detail.Select(p => new apply_detail_dto { apply_id = p.apply_id, time_line = p.timeline }).ToList();
}}

And use a DTO object to project into (new apply_detail_dto {}). Hope that helps.

edited Apr 11, 2017 at 11:57

answered Apr 11, 2017 at 8:04

Amanvir Mundra

4401 gold badge6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bofuqiang Over a year ago

My database is mysql,there no MultipleActiveResultSets option,<br/> and i pass this queryable "v_apply_detail" to my Func, but i need the same dbcontext for query performance.

Amanvir Mundra Over a year ago

Maybe eager loading will help. bgsoftfactory.net/…. "To my knowledge, MySql does not have any comparable feature. So we have to use Eager Loading, that is Entity Framework will relay on what we tell him to do about loading related data. The commented code show a first method, with .Include, while working code show a second method, with .ToList(). To see what happen with Lazy Loading, remove “.ToList()” and the parenthesis, and restart the application."

Amanvir Mundra Over a year ago

You didn't mention the structure of your POCO classes. But I would assume that there is relationship between v_apply_Detail and yoyo_apply_timelines. So instead of using DB context use navigation properties. Also adding a ToList() on TimeLine would trigger the query. Try removing that, cos you are already doing it in GetApplyList().

levent · Accepted Answer · 2017-04-11 13:13:36Z

0

this is may be helpful you

    Expression<Func<v_apply_detail, v_apply_detail2>> SelectOutApply(yoyoEntities ds,bool includetimeline = false)
    {
        if (includetimeline)
            return (p) => new v_apply_detail2
            {
                apply_id = p.apply_id,
                time_line = ds.yoyo_apply_timeline.Where(x => x.apply_id == p.apply_id).OrderByDescending(x => x.time).ToList() 
            };
        return (p) => new v_apply_detail2
            {
                apply_id = p.apply_id
            }; 

    }
    public List<v_apply_detail2> GetApplyList()
    {
        using (yoyoEntities ds = new yoyoEntities())
        {
            return datas = ds.v_apply_detail.Select(ds,SelectOutApply(true)).ToList();
        }
    }

answered Apr 11, 2017 at 13:13

levent

3,6541 gold badge16 silver badges22 bronze badges

1 Comment

bofuqiang Over a year ago

thanks。But it still leads to the following errors ,when includetimeline is true :There is already an open DataReader associated with this Connection which must be closed first.

bofuqiang · Accepted Answer · 2017-04-11 09:01:05Z

Thank Amanvir Singh Mundra.

here is my poco classes

public partial class v_apply_detail2
{
    public long apply_id { get; set; }
    public long hiring_id { get; set; }
    public string hiring_title { get; set; }
    public string publish_user_id { get; set; }
    public string publish_company_id { get; set; }
    public string user_id { get; set; }
    public Nullable<System.DateTime> pre_accept_time { get; set; }
    public sbyte join_type { get; set; }
    public Nullable<decimal> deal_pay { get; set; }
    public Nullable<System.DateTime> deal_time { get; set; }
    public Nullable<System.DateTime> start_work_time { get; set; }
    public Nullable<System.DateTime> finish_time { get; set; }
    public string master_appraise { get; set; }
    public string worker_appraise { get; set; }
    public Nullable<int> master_rating { get; set; }
    public Nullable<int> worker_rating { get; set; }
    public int status { get; set; }
    public Nullable<System.DateTime> master_rating_time { get; set; }
    public Nullable<System.DateTime> worker_rating_time { get; set; }

    public string hiring_snapshots { get; set; }

    public List<yoyo_apply_timeline> time_line { get; set; }
    public member_info member_info { get; set; }
    public company_info company_info { get; set; }
}

i has same class in my project to provide data service

so, my Problem is the query performance and Code reuse.

if i add the dbcontext in my select func ,like this

using (yoyoEntities dd = new yoyoEntities())
{
    v_apply_detail2 result = new v_apply_detail2();
    {
        result.apply_id = x.apply_id;
        result.time_line = dd.yoyo_apply_timeline.Where(p => p.apply_id == x.apply_id).OrderByDescending(p => p.time).ToList();
    }
    return result;
}

i can get the result , but the trigger many sql query . but use

return  datas = ds.v_apply_detail.Select(p => new v_apply_detail2 { apply_id = p.apply_id, time_line = ds.yoyo_apply_timeline.ToList() }).ToList();

the sql query only one。so ，i want to reuse the ds。i think the expression can do this，and i can't find the road.

Collectives™ on Stack Overflow

How To Process Entity Framework Context int Lambda Expression

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related