Using c# linq, in a where clause only include results from previous where clause-CodePudding

I am wanting to only get the SourceEndpointConfigs from the sourceEndpoints retrieved from _vwSourceEndpoints. I am not sure how to modify my where clause to get the correct results. All help appreciated. Thanks!

_vwSourceEndpoints = db.vwSourceEndpoints
    .Where(e => e.StudySourceSystemId == study.StudySourceSystemId
            & e.ScheduleId == study.ScheduleId
            & e.OrderNo == study.OrderNo
            & e.Active == true
    ).ToList();


_vwSourceEndpointConfigs = db.SourceEndpointConfigs.Where(
    e => e.Active == true
    ).ToList();

var query3 = _vwSourceEndpointConfigs.Concat(_vwSourceEndpoints.OrderBy(x => x.OrderNo).ToList();

I have come up with the below as an edit to my original code

_vwSourceEndpoints = db.vwSourceEndpoints.Where(
                    e => e.StudySourceSystemId == study.StudySourceSystemId
                    & e.ScheduleId == study.ScheduleId
                    & e.OrderNo == study.OrderNo
                    & e.Active == true).ToList();


                _vwSourceEndpointConfigs = db.SourceEndpointConfigs.Where(
                    e => _vwSourceEndpoints.All(x => x.StudySourceSystemId == study.StudySourceSystemId
                   & x.ScheduleId == study.ScheduleId
                   & x.OrderNo == study.OrderNo
                   & x.Active == true)
                    

                    ).ToList();

CodePudding user response：

This is eventually what I found to be the best working solution.

_vwSourceEndpoints = db.vwSourceEndpoints.Where(
                    e => e.StudySourceSystemId == study.StudySourceSystemId
                    & e.ScheduleId == study.ScheduleId
                    & e.OrderNo == study.OrderNo
                    & e.Active == true).ToList();

                _vwSourceEndpointConfigs = (from config in db.SourceEndpointConfigs.Where(c => c.Active == true)
                                            join ep in _vwSourceEndpoints
                                            on config.SourceEndpointId equals ep.SourceEndpointId
                                            select config).ToList();

CodePudding user response：

If both tables have same columns you can use Union, if not select the columns that you want and then use Union.

var query = context.vwSourceEndpoints
                        .Where(e => e.StudySourceSystemId == study.StudySourceSystemId
                                && e.ScheduleId == study.ScheduleId
                                && e.OrderNo == study.OrderNo
                                && e.Active == true
                        ).Union(context.SourceEndpointConfigs.Where(e => e.Active == true);