?
Solved

Can I use the SQL BETWEEN function in LINQ?

Posted on 2009-04-22
10
Medium Priority
?
1,991 Views
Last Modified: 2013-11-11
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?
0
Comment
Question by:Shepwedd
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 24202685
SELECT * FROM orders WHERE [order_date] BETWEEN #" & txtBegin & "# AND #" &txtEnd & "#"
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24202839
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
 

Author Comment

by:Shepwedd
ID: 24202852
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Shepwedd
ID: 24202870
Dhaest,

Again, how do I reference my frontend textbox control values (i.e. the dates) in Linq? Do I some how pass parameters?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24202959
Did you try something like this:

var query = from t in table
where t.date >= listview.text && t.date < listview2.text select t;
 
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24203021
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
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24203025
[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
 

Author Comment

by:Shepwedd
ID: 24205619
sree ven,

I seem to be getting the error "string was not recognised as a valid DateTime"?
0
 

Author Comment

by:Shepwedd
ID: 24205790
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
 
LVL 9

Accepted Solution

by:
Sreedhar Vengala earned 2000 total points
ID: 24210155
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question