T-SQL / EF Where Col is true and false to filter result
I have a stinking cold and can't for the life of me get my brain into gear to figure this out!
jobStops.Where(jobStop => jobStop.TrailerId == trailer.TrailerId &&
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) &&
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))
Obviously I know that we can't have where a column is true and false as that's impossible but what I'm after is the where statement to be read twice to filter itself down.
So the above query is basically saying, grab from the database the trailer row where it's delivered in the future and it's been collected in the past, so I know what's on the trailer at that time.
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0)
This deals with the items that are set for delivery in the future
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0)
This ultimately is meant to filter down the results of the above where clause so that it only shows items that have been collected.
I'm aware this is something probably really easy but the project was due for completion yesterday and I just can't get my head round it with this stinking cold.
c#
sql-server entity-framework tsql lambda edited Jan 19 at 11:32
marc_s
575k12811101257
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
add a comment |
I have a stinking cold and can't for the life of me get my brain into gear to figure this out!
jobStops.Where(jobStop => jobStop.TrailerId == trailer.TrailerId &&
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) &&
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))
Obviously I know that we can't have where a column is true and false as that's impossible but what I'm after is the where statement to be read twice to filter itself down.
So the above query is basically saying, grab from the database the trailer row where it's delivered in the future and it's been collected in the past, so I know what's on the trailer at that time.
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0)
This deals with the items that are set for delivery in the future
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0)
This ultimately is meant to filter down the results of the above where clause so that it only shows items that have been collected.
I'm aware this is something probably really easy but the project was due for completion yesterday and I just can't get my head round it with this stinking cold.
c#
sql-server entity-framework tsql lambda edited Jan 19 at 11:32
marc_s
575k12811101257
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
where you use of (jobStop.TrailerId == trailer.TrailerId),this get only a row...for this reason jobStop.IsDelivery is true or false ...so you must use (||) Instead (&&) in second filter
– Amin Sahranavard
Jan 19 at 9:25
You can use>and<instead ofCompareTo. I assume this is EF-core?
– Gert Arnold
Jan 19 at 10:48
add a comment |
I have a stinking cold and can't for the life of me get my brain into gear to figure this out!
jobStops.Where(jobStop => jobStop.TrailerId == trailer.TrailerId &&
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) &&
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))
Obviously I know that we can't have where a column is true and false as that's impossible but what I'm after is the where statement to be read twice to filter itself down.
So the above query is basically saying, grab from the database the trailer row where it's delivered in the future and it's been collected in the past, so I know what's on the trailer at that time.
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0)
This deals with the items that are set for delivery in the future
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0)
This ultimately is meant to filter down the results of the above where clause so that it only shows items that have been collected.
I'm aware this is something probably really easy but the project was due for completion yesterday and I just can't get my head round it with this stinking cold.
c#
sql-server entity-framework tsql lambda edited Jan 19 at 11:32
marc_s
575k12811101257
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
I have a stinking cold and can't for the life of me get my brain into gear to figure this out!
jobStops.Where(jobStop => jobStop.TrailerId == trailer.TrailerId &&
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) &&
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))
Obviously I know that we can't have where a column is true and false as that's impossible but what I'm after is the where statement to be read twice to filter itself down.
So the above query is basically saying, grab from the database the trailer row where it's delivered in the future and it's been collected in the past, so I know what's on the trailer at that time.
(jobStop.IsDelivery == true &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0)
This deals with the items that are set for delivery in the future
(jobStop.IsDelivery == false &&
jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0)
This ultimately is meant to filter down the results of the above where clause so that it only shows items that have been collected.
I'm aware this is something probably really easy but the project was due for completion yesterday and I just can't get my head round it with this stinking cold.
c#
sql-server entity-framework tsql lambda c#
sql-server entity-framework tsql lambda edited Jan 19 at 11:32
marc_s
575k12811101257
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
edited Jan 19 at 11:32
marc_s
575k12811101257
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
edited Jan 19 at 11:32
marc_s
575k12811101257
edited Jan 19 at 11:32
marc_s
575k12811101257
edited Jan 19 at 11:32
marc_s
575k12811101257
575k12811101257
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
asked Jan 19 at 8:55
The Angry SaxonThe Angry Saxon
6691522
6691522
where you use of (jobStop.TrailerId == trailer.TrailerId),this get only a row...for this reason jobStop.IsDelivery is true or false ...so you must use (||) Instead (&&) in second filter
– Amin Sahranavard
Jan 19 at 9:25
You can use>and<instead ofCompareTo. I assume this is EF-core?
– Gert Arnold
Jan 19 at 10:48
add a comment |
where you use of (jobStop.TrailerId == trailer.TrailerId),this get only a row...for this reason jobStop.IsDelivery is true or false ...so you must use (||) Instead (&&) in second filter
– Amin Sahranavard
Jan 19 at 9:25
You can use>and<instead ofCompareTo. I assume this is EF-core?
– Gert Arnold
Jan 19 at 10:48
where you use of (jobStop.TrailerId == trailer.TrailerId),this get only a row...for this reason jobStop.IsDelivery is true or false ...so you must use (||) Instead (&&) in second filter
– Amin Sahranavard
Jan 19 at 9:25
where you use of (jobStop.TrailerId == trailer.TrailerId),this get only a row...for this reason jobStop.IsDelivery is true or false ...so you must use (||) Instead (&&) in second filter
– Amin Sahranavard
Jan 19 at 9:25
You can use
> and < instead of CompareTo. I assume this is EF-core?– Gert Arnold
Jan 19 at 10:48
You can use
> and < instead of CompareTo. I assume this is EF-core?– Gert Arnold
Jan 19 at 10:48
add a comment |
1 Answer
1
active
oldest
votes
You can do it using Job ID
jobStops.Where(jobStop => ((jobStop.TrailerId == trailer.TrailerId && jobStop.JobId ==trailer.JobId)&& (
(jobStop.IsDelivery == true && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) ||
(jobStop.IsDelivery == false && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))))
answered Jan 19 at 9:11
GauravGaurav
58637
The combine wouldn't work though as it would show all the items with delivery in the future AND all items collected. This is a case of needing to filter the list down so it's only showing items on the trailer at that time. Items already collected and out for delivery. I wondered if it would be a case of using .except() or similar.
– The Angry Saxon
Jan 19 at 9:16
Don't you have any other identity like job id or item id in your table?
– Gaurav
Jan 19 at 9:17
Yeah we have job id.
– The Angry Saxon
Jan 19 at 9:18
yeah using job id you can do it I have updated my answer.
– Gaurav
Jan 19 at 9:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54265503%2ft-sql-ef-where-col-is-true-and-false-to-filter-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can do it using Job ID
jobStops.Where(jobStop => ((jobStop.TrailerId == trailer.TrailerId && jobStop.JobId ==trailer.JobId)&& (
(jobStop.IsDelivery == true && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) ||
(jobStop.IsDelivery == false && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))))
answered Jan 19 at 9:11
GauravGaurav
58637
The combine wouldn't work though as it would show all the items with delivery in the future AND all items collected. This is a case of needing to filter the list down so it's only showing items on the trailer at that time. Items already collected and out for delivery. I wondered if it would be a case of using .except() or similar.
– The Angry Saxon
Jan 19 at 9:16
Don't you have any other identity like job id or item id in your table?
– Gaurav
Jan 19 at 9:17
Yeah we have job id.
– The Angry Saxon
Jan 19 at 9:18
yeah using job id you can do it I have updated my answer.
– Gaurav
Jan 19 at 9:21
add a comment |
You can do it using Job ID
jobStops.Where(jobStop => ((jobStop.TrailerId == trailer.TrailerId && jobStop.JobId ==trailer.JobId)&& (
(jobStop.IsDelivery == true && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) ||
(jobStop.IsDelivery == false && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))))
answered Jan 19 at 9:11
GauravGaurav
58637
The combine wouldn't work though as it would show all the items with delivery in the future AND all items collected. This is a case of needing to filter the list down so it's only showing items on the trailer at that time. Items already collected and out for delivery. I wondered if it would be a case of using .except() or similar.
– The Angry Saxon
Jan 19 at 9:16
Don't you have any other identity like job id or item id in your table?
– Gaurav
Jan 19 at 9:17
Yeah we have job id.
– The Angry Saxon
Jan 19 at 9:18
yeah using job id you can do it I have updated my answer.
– Gaurav
Jan 19 at 9:21
add a comment |
You can do it using Job ID
jobStops.Where(jobStop => ((jobStop.TrailerId == trailer.TrailerId && jobStop.JobId ==trailer.JobId)&& (
(jobStop.IsDelivery == true && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) ||
(jobStop.IsDelivery == false && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))))
answered Jan 19 at 9:11
GauravGaurav
58637
You can do it using Job ID
jobStops.Where(jobStop => ((jobStop.TrailerId == trailer.TrailerId && jobStop.JobId ==trailer.JobId)&& (
(jobStop.IsDelivery == true && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) >= 0) ||
(jobStop.IsDelivery == false && jobStop.JobStopDateTime.Value.Date.CompareTo(date.AddDays(x).Date) <= 0))))
answered Jan 19 at 9:11
GauravGaurav
58637
edited Jan 19 at 9:20
answered Jan 19 at 9:11
GauravGaurav
58637
answered Jan 19 at 9:11
GauravGaurav
58637
answered Jan 19 at 9:11
GauravGaurav
58637
58637
The combine wouldn't work though as it would show all the items with delivery in the future AND all items collected. This is a case of needing to filter the list down so it's only showing items on the trailer at that time. Items already collected and out for delivery. I wondered if it would be a case of using .except() or similar.
– The Angry Saxon
Jan 19 at 9:16
Don't you have any other identity like job id or item id in your table?
– Gaurav
Jan 19 at 9:17
Yeah we have job id.
– The Angry Saxon
Jan 19 at 9:18
yeah using job id you can do it I have updated my answer.
– Gaurav
Jan 19 at 9:21
add a comment |
The combine wouldn't work though as it would show all the items with delivery in the future AND all items collected. This is a case of needing to filter the list down so it's only showing items on the trailer at that time. Items already collected and out for delivery. I wondered if it would be a case of using .except() or similar.
– The Angry Saxon
Jan 19 at 9:16
Don't you have any other identity like job id or item id in your table?
– Gaurav
Jan 19 at 9:17
Yeah we have job id.
– The Angry Saxon
Jan 19 at 9:18
yeah using job id you can do it I have updated my answer.
– Gaurav
Jan 19 at 9:21
The combine wouldn't work though as it would show all the items with delivery in the future AND all items collected. This is a case of needing to filter the list down so it's only showing items on the trailer at that time. Items already collected and out for delivery. I wondered if it would be a case of using .except() or similar.
– The Angry Saxon
Jan 19 at 9:16
The combine wouldn't work though as it would show all the items with delivery in the future AND all items collected. This is a case of needing to filter the list down so it's only showing items on the trailer at that time. Items already collected and out for delivery. I wondered if it would be a case of using .except() or similar.
– The Angry Saxon
Jan 19 at 9:16
Don't you have any other identity like job id or item id in your table?
– Gaurav
Jan 19 at 9:17
Don't you have any other identity like job id or item id in your table?
– Gaurav
Jan 19 at 9:17
Yeah we have job id.
– The Angry Saxon
Jan 19 at 9:18
Yeah we have job id.
– The Angry Saxon
Jan 19 at 9:18
yeah using job id you can do it I have updated my answer.
– Gaurav
Jan 19 at 9:21
yeah using job id you can do it I have updated my answer.
– Gaurav
Jan 19 at 9:21
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54265503%2ft-sql-ef-where-col-is-true-and-false-to-filter-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
where you use of (jobStop.TrailerId == trailer.TrailerId),this get only a row...for this reason jobStop.IsDelivery is true or false ...so you must use (||) Instead (&&) in second filter
– Amin Sahranavard
Jan 19 at 9:25
You can use
>and<instead ofCompareTo. I assume this is EF-core?– Gert Arnold
Jan 19 at 10:48