This question is locked. New answers and comments are not allowed.
Guys this question really is just my non-comprehension of LINQ so I apologize, but I really could use some advice here. Basically what I need to do is to combine these two LINQ queries into one. One of them is basically a subquery of the other to filter out latest record.
Basically what I have is this
works great no issues except I get a bunch of records I do not need. I am only needing the latest record. I found out you apparently can't call methods from with in a LINQ query which is sad because I think I would have already had this licked if you could. The second query looks like this.
What I was hoping to do was something like this.
but as I said above you can't call methods from a query :(. What I have basically hacked it up to is this monstrosity.
It works, but it is so so so slow it's not a feasible answer. I did see where people were doing subqueries in LINQ and this is my attempt at combing these two methods into one LINQ query. This does not work.
Anyhow I am really struggling with this one so if any one has any pointers I would forever be greatful.
Thanks!
Basically what I have is this
var result = (from c in scope.Extent<PtAMember>() |
where (c.LastName.ToLower().Contains(param.ToLower()) || c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)) |
select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName); |
works great no issues except I get a bunch of records I do not need. I am only needing the latest record. I found out you apparently can't call methods from with in a LINQ query which is sad because I think I would have already had this licked if you could. The second query looks like this.
public int GetEffectiveCertification(int participantPK) |
{ |
try |
{ |
scope = InfrastructureScopeProvider.GetNewObjectScope(); |
scope.Transaction.Begin(); |
var result = (from c in scope.Extent<PtApprovedCertification>() |
where c.FkosParticipant.Equals(participantPK) && c.EffectiveDate <= DateTime.Now && |
(c.FkMLSosCodeActionType != 1057 || c.FkMLSosCodeActionType != 1059 || c.FkMLSosCodeActionType != 1060 || c.FkMLSosCodeActionType != 1062 || |
c.FkMLSosCodeActionType != 1063 || c.FkMLSosCodeActionType != 1064) |
orderby c.EffectiveDate descending, c.Pk descending |
select c).Single(); |
return result.Pk; |
} |
catch (Exception ex) |
{ |
return 0; |
} |
} |
What I was hoping to do was something like this.
var result = (from c in scope.Extent<PtAMember>() |
where (c.LastName.ToLower().Contains(param.ToLower()) || c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)) && |
c.PtApprovedCertification.Pk.Equals(GetEffectiveCertification(c.PtApprovedCertification.OsParticipant.Pk)) |
select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName); |
public ObservableCollection<PtAMember> SimpleResidentSearch(string param) |
{ |
try |
{ |
scope = InfrastructureScopeProvider.GetNewObjectScope(); |
scope.Transaction.Begin(); |
ObservableCollection<PtAMember> list = new ObservableCollection<PtAMember>(); |
var result = (from c in scope.Extent<PtAMember>() |
where (c.LastName.ToLower().Contains(param.ToLower()) || c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)) |
select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName); |
//stores most recent cert PK |
int certPK = 0; |
//stores current resident pk |
int currentPK = 0; |
//stores previous resident PK |
int oldPK = 0; |
foreach (PtAMember resident in result) |
{ |
currentPK = resident.PtApprovedCertification.OsParticipant.Pk; |
if (oldPK != currentPK) |
{ |
//set oldPK to new pk so that we can compare later |
oldPK = currentPK; |
certPK = GetEffectiveCertification(resident.PtApprovedCertification.OsParticipant.Pk); |
} |
//Only Add residents who represent effective cert |
if (resident.PtApprovedCertification.Pk == certPK) |
{ |
list.Add(resident); |
} |
} |
return list; |
} |
catch (Exception ex) |
{ |
return null; |
} |
} |
It works, but it is so so so slow it's not a feasible answer. I did see where people were doing subqueries in LINQ and this is my attempt at combing these two methods into one LINQ query. This does not work.
var result = (from c in scope.Extent<PtAMember>() |
from p in scope.Extent<PtApprovedCertification>() |
where c.FkptACertification == p.Pk && p.FkosParticipant.Equals(c.PtApprovedCertification.OsParticipant.Pk) && p.EffectiveDate <= DateTime.Now && |
(p.FkMLSosCodeActionType != 1057 || p.FkMLSosCodeActionType != 1059 || p.FkMLSosCodeActionType != 1060 || p.FkMLSosCodeActionType != 1062 || |
p.FkMLSosCodeActionType != 1063 || p.FkMLSosCodeActionType != 1064) && |
(c.LastName.ToLower().Contains(param.ToLower()) || |
c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)) |
select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName); |
Anyhow I am really struggling with this one so if any one has any pointers I would forever be greatful.
Thanks!