Can I use the SQL BETWEEN function in LINQ?

Firstly I'm not too sure how to use the SQL BETWEEN function in the first place but what I'm hoping to do is take two user textbox entries (these will be dates) from my frontend listview control and filter the data to be shown in the listview grid on these values through my Linq select query, i.e. I think the SQL would be something like:

SELECT *
FROM orders
WHERE order_date between to_date ('22/03/2009', 'dd/MM/yyyy')
AND to_date ('22/04/2009', 'dd/MM/yyyy');

So can this be done through Linq?
ShepweddAsked:
Who is Participating?
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Think you should be able to do  with your ReportButton_click
protected void ReportButton_Click(object sender, EventArgs e)
    {
         TrustSystemDataContext db = new TrustSystemDataContext();
 
        var query = from tt in db.Trusts
                    join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
                    join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
                    join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
                    where cdtcd.DueDate >= Convert.ToDateTime(tbSearchDateFrom.Text) && cdtcd.DueDate <= Convert.ToDateTime(tbSearchDateTo.Text)
                    orderby tt.ClientName ascending
                    select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustTypeCode, tt.TrustNotes, cdtcd.CrucialNotes, ptp.PaymentNotes, felt.FeeExecDescription, plt.PartnerDescription, tlt.TypeDescription, TrustTypeDescription = ttlt.TrustTypeDescription == null ? "" : ttlt.TrustTypeDescription, cdtcd.DueDate, cdel.EventDescription, pdl.DayDescription, pml.MonthDescription, pyl.YearDescription, ppfl.FrequencyDescription, ptp.Amount };
 
        e.Result = query;
    }

Open in new window

0
 
jppintoCommented:
SELECT * FROM orders WHERE [order_date] BETWEEN #" & txtBegin & "# AND #" &txtEnd & "#"
0
 
DhaestCommented:
The obvious answer would be something like...


var query = from t in table
where t.date >= new DateTime(2007,9,9) && t.date < new DateTime(2008,1,1) select t;

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ShepweddAuthor Commented:
When you write "#" is this to represent my frontend listview textbox control values, i.e. the dates? How do I actually reference the parameters from these controls in Linq?
0
 
ShepweddAuthor Commented:
Dhaest,

Again, how do I reference my frontend textbox control values (i.e. the dates) in Linq? Do I some how pass parameters?
0
 
DhaestCommented:
Did you try something like this:

var query = from t in table
where t.date >= listview.text && t.date < listview2.text select t;
 
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
You can refer you textbox in query.

simple example:

dbDataContext db = new dbDataContext();
txtFrom.Text = DateTime.Now.AddYears(-50).ToString();  //textbox
txtTo.Text = DateTime.Now.ToString(); //textbox
var query = from d in db.Employees
                where d.BirthDate >= Convert.ToDateTime(txtFrom.Text)
                where d.BirthDate <= Convert.ToDateTime(txtTo.Text)
                select d;

Equvi SQL Statement:
SELECT  [t0].[EmployeeID],
                  [t0].[LastName],
                  [t0].[FirstName],
                  [t0].[Title],
                  [t0].[TitleOfCourtesy],
                 [t0].[BirthDate]
FROM [dbo].[Employees] AS [t0]
WHERE         ([t0].[BirthDate] <= @p0)
              AND ([t0].[BirthDate] >= @p1)
 
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
[OR]
var query = from d in db.Employees
                                   where d.BirthDate >= Convert.ToDateTime(txtFrom.Text) 
                                   && d.BirthDate <= Convert.ToDateTime(txtTo.Text)
                                   select d;

Open in new window

0
 
ShepweddAuthor Commented:
sree ven,

I seem to be getting the error "string was not recognised as a valid DateTime"?
0
 
ShepweddAuthor Commented:
sree ven,

I think it's because there are no actual dates in the textboxes due to the where filter being set within my listviews linqdatasource OnSelecting event. I guess I need to put the filtered between dates linq query within a button click event but can a linq query be attached to a simple button click event? Does it not have to be a linqdatasource event? Attached is my linqdatasource OnSelecting event with the linq query and below that is my button click event where I want to place the between dates filter linq query...
protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        TrustSystemDataContext db = new TrustSystemDataContext();
 
        var query = from tt in db.Trusts
                    join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
                    join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
                    join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
                    orderby tt.ClientName ascending
                    select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustTypeCode, tt.TrustNotes, cdtcd.CrucialNotes, ptp.PaymentNotes, felt.FeeExecDescription, plt.PartnerDescription, tlt.TypeDescription, TrustTypeDescription = ttlt.TrustTypeDescription == null ? "" : ttlt.TrustTypeDescription, cdtcd.DueDate, cdel.EventDescription, pdl.DayDescription, pml.MonthDescription, pyl.YearDescription, ppfl.FrequencyDescription, ptp.Amount };
 
        e.Result = query;
    }
 
******************************************
 
protected void ReportButton_Click(object sender, EventArgs e)
    {
         TrustSystemDataContext db = new TrustSystemDataContext();
 
        var query = from tt in db.Trusts
                    join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
                    join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
                    join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
                    where cdtcd.DueDate >= Convert.ToDateTime(tbSearchDateFrom.Text) && cdtcd.DueDate <= Convert.ToDateTime(tbSearchDateTo.Text)
                    orderby tt.ClientName ascending
                    select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustTypeCode, tt.TrustNotes, cdtcd.CrucialNotes, ptp.PaymentNotes, felt.FeeExecDescription, plt.PartnerDescription, tlt.TypeDescription, TrustTypeDescription = ttlt.TrustTypeDescription == null ? "" : ttlt.TrustTypeDescription, cdtcd.DueDate, cdel.EventDescription, pdl.DayDescription, pml.MonthDescription, pyl.YearDescription, ppfl.FrequencyDescription, ptp.Amount };
 
        e.Result = query;
    }

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.