How to convert sql query to linq query?

Question

0.00/5 (No votes)

See more:

Hello,

The linq query is unable to read the "getdate()" sql function, as it outputs the following error -- "The name 'getdate' does not exist in the current context"

Updated Linq query:

C#

public string getSubs(string username)
        public string getSubs(string username)
        {

            var query = from s in db.Subscriptions
                        join u in db.UserDetails on s.sUID equals u.uID
                        where s.sPriceABS_ExpiryDate >= DateTime.Now &&
                        s.sPID.Value == 163 &&
                        u.uUsername == username
                        select query;
         }

       }

Is there a way to get linq to read the "getdate()" sql function? Many thanks

Posted 12-Feb-14 23:39pm

miss786

Updated 17-Feb-14 23:37pm

v3

Add a Solution

Comments

Prasad Avunoori 13-Feb-14 5:44am

Why dont you use DateTime.Now() instead of Getdata()?

3 solutions

Solution 2

use native language date and time class to get date

C#

public string getSubs(string username)
        {
            var query = from s in db.Subscriptions
                        join u in db.UserDetails on s.sUID equals u.uID
                        where s.sPriceABS_ExpiryDate >= DateTime.Now &&
                        s.sPID = "163"
                        select new
                        {
                            u.uID,
                            u.uUsername,
                            u.uPassword,
                            s.sPriceABS_ExpiryDate
                        };

       }

Posted 13-Feb-14 0:00am

AvinashAher

Comments

miss786 13-Feb-14 6:08am

Thank you for your response and solution, but when i added "dataTime.now", it throws -- Operator '&&' cannot be applied to operands of type 'bool' and 'int?'-- error.

AvinashAher 13-Feb-14 6:19am

use s.sPID == "163" instead s.sPID = "163"

that will work fine

Pete O'Hanlon 13-Feb-14 6:42am

And what happens if the server time doesn't match the client time? Suppose the server is situated halfway round the world and is operating on a local time?

AvinashAher 13-Feb-14 6:53am

Hi Dear,

LINQ is work with collection witch is integrate with native language, i don't think so any other way to get current date and Time.

Pete O'Hanlon 13-Feb-14 7:13am

Yes there is. I linked to it in my answer. As long as you are working with Linq to SQL (which she obviously is if she wants to use GETDATE) then you can get the date from the server.

Solution 3

This will work fine. it is related to syntax error. Use proper syntax to write query

C#

public string getSubs(string username)
        {
            var query = from s in db.Subscriptions
                        join u in db.UserDetails on s.sUID equals u.uID
                        where s.sPriceABS_ExpiryDate >= DateTime.Now &&
                        s.sPID == "163"
                        select new
                        {
                            u.uID,
                            u.uUsername,
                            u.uPassword,
                            s.sPriceABS_ExpiryDate
                        };

       }

Posted 13-Feb-14 0:21am

AvinashAher

Comments

miss786 14-Feb-14 5:33am

Thank you so much for your response. I manage get my linq query updated (please see the above original post), but I am still experiencing the following compiling error - Cannot use local variable 'query' before it is declared. I am aiming to use the linq to query to return username based on the where conditions. Is this right approach to an linq query for the getting the username output? I really appreciate your help and time.

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)

Pete O'Hanlon · Accepted Answer · 2014-02-12T23:42:00

Solution 1

You can use the technique I describe here[^] to do this. Make sure you follow the instructions exactly.

Posted 12-Feb-14 23:42pm

Pete O'Hanlon