LINQ: Count records

  • Thread starter Thread starter Adam K.
  • Start date Start date
A

Adam K.

Hi,
I have stored procedure that returns some records from table, i.e. like
this:
CREATE PROCEDURE dbo.UsersGet AS
BEGIN
SELECT * FROM Users;
END

In c# i can acces to this stored procedure by code:

SampleDatabaseContext ctx = new SampleDatabaseContext(connectionString);
foreach(UsersGetResult usr in ctx.UsersGet()) {
Console.WriteLine(usr.UserName);
}
But i need know how many records was returned by ctx.UsersGet() before i
execute foreach() loop.
How can i get this row counter?

Best regards

Adam
 
Adam said:
Hi,
I have stored procedure that returns some records from table, i.e. like
this:
CREATE PROCEDURE dbo.UsersGet AS
BEGIN
SELECT * FROM Users;
END

In c# i can acces to this stored procedure by code:

SampleDatabaseContext ctx = new SampleDatabaseContext(connectionString);
foreach(UsersGetResult usr in ctx.UsersGet()) {
Console.WriteLine(usr.UserName);
}
But i need know how many records was returned by ctx.UsersGet() before i
execute foreach() loop.
How can i get this row counter?
Click to expand...

Since 'ctx' seems to be of type System.Data.Linq.DataContext
and UsersGet() probably returns the data as
System.Data.Linq.Table<UsersGetResult>
the extension-method "Count()" will give you the SP's rowcount.


SampleDatabaseContext ctx = new SampleDatabaseContext(connectionString);
System.Data.Linq.Table<UsersGetResult> users = ctx.UsersGet();
int rowCount = users.Count();


MfG,
Alex
 
Adam said:
Hi,
I have stored procedure that returns some records from table, i.e. like
this:
CREATE PROCEDURE dbo.UsersGet AS
BEGIN
SELECT * FROM Users;
END

In c# i can acces to this stored procedure by code:

SampleDatabaseContext ctx = new SampleDatabaseContext(connectionString);
foreach(UsersGetResult usr in ctx.UsersGet()) {
Console.WriteLine(usr.UserName);
}
But i need know how many records was returned by ctx.UsersGet() before i
execute foreach() loop.
How can i get this row counter?
Click to expand...

Try whether

ctx.UsersGet().Count()

gives you the count.

But if you do
int count = ctx.UsersGet().Count();
and then
foreach (UsersGetResult usr in ctx.UsersGet())
{
...
}
you might pull the data twice from the database server so maybe
var users = ctx.UsersGet().ToList();
int count = users.Count();
foreach (var user in users)
{
...
}
might be more efficient.

If the above does not help then please post the .NET code generated for
the stored procedure.
 
Alexander said:
Since 'ctx' seems to be of type System.Data.Linq.DataContext
and UsersGet() probably returns the data as
System.Data.Linq.Table<UsersGetResult>
the extension-method "Count()" will give you the SP's rowcount.


SampleDatabaseContext ctx = new SampleDatabaseContext(connectionString);
System.Data.Linq.Table<UsersGetResult> users = ctx.UsersGet();
int rowCount = users.Count();
Click to expand...

Of course this will still execute the SP and fetch the data from
the DB-table "Users".

If you'd do the Count() directly on the mapped LINQ-table
- default name of the getter would be "TUserss" - LINQ-To-SQL
would translate the "Count()" extension-method into a
"SELECT Count(*) FROM Users;".

This wouldn't fetch the rows, thus be more effective.

MfG,
Alex
 
User "Alexander Mueller said:
Since 'ctx' seems to be of type System.Data.Linq.DataContext
and UsersGet() probably returns the data as
System.Data.Linq.Table<UsersGetResult>
the extension-method "Count()" will give you the SP's rowcount.


SampleDatabaseContext ctx = new SampleDatabaseContext(connectionString);
System.Data.Linq.Table<UsersGetResult> users = ctx.UsersGet();
Click to expand...

Not sure, ctx.UsersGet() returns
System.Data.Linq.ISingleResult<UsersGetResult> which not have
method/property Count().

Any ideas?

Best regards
Adam
 
User "Martin Honnen said:
Try whether

ctx.UsersGet().Count()

gives you the count.

But if you do
int count = ctx.UsersGet().Count();
and then
foreach (UsersGetResult usr in ctx.UsersGet())
{
...
}
Click to expand...

Hi, code generated for this stored procedure look like this:

[Function(Name="dbo.UsersGet")]
public ISingleResult<UsersGetResult> UsersGet(){
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<UsersGetResult>)(result.ReturnValue));
}

Any ideas?

Best regards,

Adam
 
Adam said:
Not sure, ctx.UsersGet() returns
System.Data.Linq.ISingleResult<UsersGetResult> which not have
method/property Count().
Click to expand...

ISingleResult<T> implements IEnumerable<T> and that way should have a
Count method.
 
Adam said:
Hi, code generated for this stored procedure look like this:

[Function(Name="dbo.UsersGet")]
public ISingleResult<UsersGetResult> UsersGet(){
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<UsersGetResult>)(result.ReturnValue));
}

Any ideas?
Click to expand...

Then UsersGet().Count() should do, see
http://msdn.microsoft.com/en-us/library/cc472438.aspx
which lists a Count method.
 
Martin Honnen said:
Hi, code generated for this stored procedure look like this:

[Function(Name="dbo.UsersGet")]
public ISingleResult<UsersGetResult> UsersGet(){
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<UsersGetResult>)(result.ReturnValue));
}

Any ideas?
Click to expand...

Then UsersGet().Count() should do, see
http://msdn.microsoft.com/en-us/library/cc472438.aspx
which lists a Count method.
Click to expand...

I red this document, but when i want read this value
int recCount = ctx.UsersGet().Count();
I got compile error like this:

Error 2 'System.Data.Linq.ISingleResult<UsersGetResult>' does not contain a
definition for 'Count' and no extension method 'Count' accepting a first
argument of type 'System.Data.Linq.ISingleResult<UsersGetResult>' could be
found (are you missing a using directive or an assembly reference?)


What's wrong?

I use Visual Web Developer 2008 Express Edition, .NetFramework 3.5.

Please help me.

Regards
Adam
 
Adam said:
I red this document, but when i want read this value
int recCount = ctx.UsersGet().Count();
I got compile error like this:

Error 2 'System.Data.Linq.ISingleResult<UsersGetResult>' does not
contain a definition for 'Count' and no extension method 'Count'
accepting a first argument of type
'System.Data.Linq.ISingleResult<UsersGetResult>' could be found (are you
missing a using directive or an assembly reference?)


What's wrong?

I use Visual Web Developer 2008 Express Edition, .NetFramework 3.5.
Click to expand...

Do you have

using System.Linq;

in your source code and a reference to System.Core in your references
(in solution explorer)?
 
Martin Honnen said:
Do you have

using System.Linq;

in your source code and a reference to System.Core in your references (in
solution explorer)?
Click to expand...

BINGO !
It's working now, thank you, thank you, thank you :)

Best regards
Adam
 
Back
Top