Sql to LINQ query no result

Question

0.00/5 (No votes)

See more:

I try this SQL query and this query show results

SQL

select  distinct 
tblvv.MID,
tblrv.ownername,

from tblvv
join tblrv on tblrv.ID=tblvv.MID
join tblre on tblre.RID = tblrv.RID
WHERE tblre.Region ='uk'  and
StartDate = '2014-02-01 00:00:00.000' AND 
EndDate = '2014-02-28 23:59:59.000'
order by
tblrv.ownername

What I have tried:

and then i try to convert this query in LINQ

SQL

try
            {
              
                DateTime frmdate = Convert.ToDateTime(fromdate.Value.Trim().Split('T')[0]);
                DateTime tdatee = Convert.ToDateTime(todate.Value.Trim().Split('T')[0]);
                string regionvalue = regiondrop.SelectedValue;
                T1 ts = new Ts1();
               
                var dq = (from vv in ts.tblvv
                          join rv in ts.tblrv on vv.MID equals rv.ID
                          join re in ts.tblre on rv.RID equals re.RID
                          where
                          re.Region == regionvalue
                         && re.StartDate == frmdate
                          && re.EndDate == tdatee
                          orderby rv.OwnerName
                          select new
                          {
                            
                              ownername = rv.OwnerName,
                              MID= VV.MID,
                              

                          }).ToList();
                         
                GridView1.DataSource = dq;
                GridView1.DataBind();

            }
            catch (Exception)
            {
                GridView1.Visible = false;
                Label4.Text = ("No Data");

            }

but this above query not show any result

when i write || this sign on this line

SQL

&& re.StartDate == frmdate

like

SQL

|| re.StartDate == frmdate

then query shows so many records and this records are totally different from records which is in SQL query

also when i set break point there is not any error occurred

any solution?

Posted 26-Jun-16 21:52pm

super_user

Updated 28-Jun-16 0:33am

Add a Solution

Comments

Zafar Sultan 27-Jun-16 4:25am

Are you getting the values of frmdat, tdatee and regionvalue same as you are passing in SQL query? Have you tried putting a debugger and checked these values?

super_user 27-Jun-16 4:26am

yes i am getting these values

super_user 27-Jun-16 4:27am

when is select UK from drop-down and select these dates from calendar
from date '2014-02-01' AND
to date '2014-02-28'.. i am getting these values but i can not get data in gridview

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-06-28T00:33:00

The problem appears to be the time part of your datetime fields - note that in your SQL you are checking

SQL

StartDate = '2014-02-01 00:00:00.000' AND 
EndDate = '2014-02-28 23:59:59.000'

but you state the dates on your calendar are '2014-02-01' and '2014-02-28'.
Try comparing the date only - there are 2 potential solutions on this CP article - LINQ query to compare only date part of DateTime[^]

Incidentally, the reason that you get lots of results when you change

C#

&& re.StartDate == frmdate

to

C#

|| re.StartDate == frmdate

is because you are mixing ANDs and ORs without any parentheses.

C#

 where
 re.Region == regionvalue
|| re.StartDate == frmdate
 && re.EndDate == tdatee

Putting parentheses in place isn't worth it as you will still hit the problem with the time.