CODEWITHSUNDEEP
.netsqlvisual-studio-2010linq-to-sql
daidai
daidai

Reputation: 541

Trouble with SQL statement for count(*)

I am using SQL Data Server created in Visual Studio 2010.I want to see number of users with some username.

var x = from u in db.UserInfoes
                        where
                        u.Password == password &&
                        u.Username == username
                        select count(*);

My problem is count(*).VS doesnt accept this.Is there a way to write this?

Upvotes: 2

Views: 92

Answers (5)

Neil N
Neil N

Reputation: 25268

You could also do it this way:

var x = db.UserInfoes
    .Count(u => u.Password == password && u.Username == username);

The where clause can be seen of as redundant, as you can put your filter(the predicate) right inside the Count() method

But since you are checking for a SINGLE record (assuming only one user matches a username and password combo) You actually want to use the ANY method and get a Boolean

var x = db.UserInfoes
    .Any(u => u.Password == password && u.Username == username);

Any will be slightly faster than count, as it will return true as soon as it finds a match, instead of going through the entire table to ensure an exact count.

Upvotes: 4

Venkata Krishna
Venkata Krishna

Reputation: 15112

var x = from u in db.UserInfoes
        where u.Password == password &&
              u.Username == username
        select u;

Use

int i = (x != null) ? x.Count() : 0;

Upvotes: 0

Uriil
Uriil

Reputation: 12628

Actully you can set up predicate in Count extension:

   var x = db.UserInfoes.Count(u => u.Password == password && u.Username == username)

Upvotes: 2

John Woo
John Woo

Reputation: 263803

using extension method,

var x = db.UserInfoes
          .Where(u => u.Password == password && u.Username == username)
          .Count();

Upvotes: 1

gzaxx
gzaxx

Reputation: 17600

You have to count on returned collection.

var x = (from u in db.UserInfoes
         where u.Password == password &&
               u.Username == username
         select u).Count();

Upvotes: 3

Related Questions