[RESOLVED] linq statement that returns a count

[trevorjeaton]

hi all,

i have a linq statement that returns a resultset from a domainservice - what i'm really after is the number of records, not the records themselves and for the life of me cannot get it....code is below and any help would be appreciated:

The table is called login and is associated with its entity dataset called logins - the project is called EstimatorSilverlight

Public Function GetLoginsByName(ByVal username As String, ByVal password As String) As IQueryable(Of login)
        Dim count As Integer = Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password).AsQueryable().Count()
        Return count
    End Function

The error is "Value of type 'Integer' cannot be converted to 'System.Linq.IQueryable(Of EstimatorSilverlight.Web.login)'.


the original code that successfully returns the resultset is here:

Return Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password)

[techgnome]

try this:

Public Function GetLoginsByName(ByVal username As String, ByVal password As String) As IQueryable(Of login)
        Dim count As Integer = Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password).ToList.Count
        Return count
    End Function

See if that helps.

Unfortunately I don't have a better example. Seems in most of the cases where I'm checking for a count, it's on a List(of) that was created as part of a LINQ statement. So that should work.

-tg

[trevorjeaton]

testing now.....

[trevorjeaton]

no joy - get the same thing:

"Value of type 'Integer' cannot be converted to 'System.Linq.IQueryable(Of EstimatorSilverlight.Web.login)'.

this is also code in a domain service if that makes a difference.....i find it strange that the result set returns absolutely perfectly but for the life of me i just cannot find where to count the darn results....this is driving me batty.....

[trevorjeaton]

i've also tried .AsEnumerable.Count as well as .AsQueryable.Count and its always the same - value of type integer...blah blah blah........grrrrr :-)

[techgnome]

OI! that'll teach me... the problem isn't your LINQ.... it's the FUNCTION!

Public Function GetLoginsByName(ByVal username As String, ByVal password As String) As IQueryable(Of login)

you need to change the function (and probably the name too) to return an INTEGER... that's what the issue is
Public Function GetLoginCountByName(ByVal username As String, ByVal password As String) As Integer


-tg

[trevorjeaton]

at this point i'm defining the function as Integer instead of as IQueryable and will see if that works.....more to follow...

[trevorjeaton]

hehehhe - looks like we were both thinking the same thing at the same time......trying with Integer as we speak

[trevorjeaton]

interesting - i have a label on the page as a quick debugger to see the value -its returning 'System.ServiceMod' from the function.....here's what i changed it to in the domainservice:

Public Function countloginsmatching(ByVal username As String, ByVal password As String) As Integer

        Return Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password).Count

    End Function

and here's the code that calls it:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnLogin.Click

        Dim username As String = tbUserName.Text
        Dim password As String = pbPassword.Password

        Dim context = New LoginContext()

        context.Load(context.GetLoginsByNameQuery(username, password))

        Label3.Content = context.countloginsmatching(username, password)

        DataGrid1.ItemsSource = context.logins

    End Sub

the datagrid and the label are only to track what's being returned by the way - ultimately i'm looking for a value of 1 or 0 and that'll be a match in the table and allow entry.....

[trevorjeaton]

correction - the label was truncated - its returning "system.servicemodel.domainservices.client.invokeoperation `1[System.Int32]'

[trevorjeaton]

if i change label3.content to this:

Label3.Content = context.countloginsmatching(username, password).Value

It always returns 0 even if i get a match.....at which point of course it should return a 1

[techgnome]

What's the .Content property? Labels have a .Text property.

Label3.Text= context.countloginsmatching(username, password).ToString

Two changes there...

-tg

EDIT - also... might want to check the case of your strings... I can't remember if LINQ is case sensitive (in my use everything is upper cased, so it's never been an issue).

[trevorjeaton]

silverlight/wpf - hence the .content

[techgnome]

ah... that's where this should have been then... it's not really a DB question... I'll ask a mod to move it. Might get better results there.

-tg

[trevorjeaton]

thanks for all the help thus far - i'm about 99% of the way there now......its just that return value....not sure why its always zero......

thx again

[si_the_geek]

thread moved to WPF forum (thanks for letting us know tg )

[trevorjeaton]

just tried this on the domainservice side:

Public Function countloginsmatching(ByVal username As String, ByVal password As String) As Integer

        Return Me.ObjectContext.logins.Distinct().Count(Function(t) t.username = username AndAlso t.password = password)

    End Function

here's the buttonclick code:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnLogin.Click

        Dim username As String = tbUserName.Text
        Dim password As String = pbPassword.Password

        Dim context = New LoginContext()

        context.Load(context.GetLoginsByNameQuery(username, password))

        Label3.Content = context.countloginsmatching(username, password).Value

        DataGrid1.ItemsSource = context.logins

    End Sub

still returns a 0 when it should be returning a 1 - the datagrid populates with the correct data if a match is found but i can't get a count of 1 returned



any takers?

[trevorjeaton]

now tried this:

Public Function countloginsmatching(ByVal username As String, ByVal password As String) As Integer
        Dim credentials As List(Of login)
        credentials = Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password).ToList()
        Return credentials.Count
End Function

and still returning zero........am i missing something with regard to how values are passed back to the calling page from a domainservice? and if so, why is my test datagrid populating flawlessly?

[techgnome]

put a break point on this line: Return credentials.Count

Check credentials... make sure that it contains a record...
If it doesn't, then there's something else wrong (probably with the username and password you are passing in)... again... I don't remember if there's a case sensitivity problem in LINQ...

-tg

[trevorjeaton]

will give it a shot.....

[trevorjeaton]

weird......viewing the locals gives me a count = 1 for credentials.count on a successful set of credentials and a count of 0 on unsuccessful credentials, so its doing it correctly, its just not passing it from the server side to the client side correctly or my code isn't correct on how to display the count value.........could it be something as simple as populating the label incorrectly???

so that now turns my focus back to this:

 Label3.Content = context.countloginsmatching(username, password).Value.ToString

[techgnome]

interesting... OK let's take things a step further...

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnLogin.Click

        Dim username As String = tbUserName.Text
        Dim password As String = pbPassword.Password
        Dim totalCount As Integer = -1
        Dim context = New LoginContext()

        context.Load(context.GetLoginsByNameQuery(username, password))
        totalCount = context.countloginsmatching(username, password).Value 'First try this with .Value ... put a breakpoint on the next line and see what totalCount is...
'If it doesn't work... take the .Value off of the function call... the function returns an integer... 
        Label3.Content = context.countloginsmatching(username, password).Value

        DataGrid1.ItemsSource = context.logins

    End Sub

-tg

[trevorjeaton]

will do - testing now

[trevorjeaton]

totalcount shows 0 at the breakpoint and when i remove the .Value i get the following:

Value of type 'System.ServiceModel.DomainServices.Client.InvokeOperation(Of Integer)' cannot be converted to 'Integer'.

count still shows a correct count of 1 on the server side......

[techgnome]

can you post your entire code for LoginContext()? I'll admit... I'm stumped... dang... I have the perfect lolcat for that and I don't have my links handy... :P anyways.. .clearly there's something else going on... not sure what it is... but that last error makes no sense to me.

I see that you have context.logins in the line that follows.... what if you do this:
Label3.Content = context.logins.Count ';may or may not need .ToString on the end of that too...

-tg

[trevorjeaton]

yep, i hear ya - this was supposed to be a 30 second access to the database on a 1=yes we know you and 0=no we don't know you - and here we are a few days later....i like to think i "kinda" know what i'm doing but i'm clearly missing something on this puppy.......

testing the changes now - will post results in a sec

[trevorjeaton]

here is all the code for loginservice.vb:

Option Compare Binary
Option Infer On
Option Strict On
Option Explicit On

Imports EstimatorSilverlight.Web
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations
Imports System.Data
Imports System.Linq
Imports System.ServiceModel.DomainServices.EntityFramework
Imports System.ServiceModel.DomainServices.Hosting
Imports System.ServiceModel.DomainServices.Server

'Implements application logic using the LoginEntities context.
' TODO: Add your application logic to these methods or in additional methods.
' TODO: Wire up authentication (Windows/ASP.NET Forms) and uncomment the following to disable anonymous access
' Also consider adding roles to restrict access as appropriate.
'<RequiresAuthentication> _

<EnableClientAccess()>  _
Public Class LoginService
    Inherits LinqToEntitiesDomainService(Of LoginEntities)
    
    'TODO:
    ' Consider constraining the results of your query method.  If you need additional input you can
    ' add parameters to this method or create additional query methods with different names.
    'To support paging you will need to add ordering to the 'logins' query.

    <Query(IsDefault:=True)> _
    Public Function GetLogins() As IQueryable(Of login)
        Return From login In Me.ObjectContext.logins Order By login.username
    End Function
    
    Public Sub InsertLogin(ByVal login As login)
        If ((login.EntityState = EntityState.Detached)  _
                    = false) Then
            Me.ObjectContext.ObjectStateManager.ChangeObjectState(login, EntityState.Added)
        Else
            Me.ObjectContext.logins.AddObject(login)
        End If
    End Sub
    
    Public Sub UpdateLogin(ByVal currentlogin As login)
        Me.ObjectContext.logins.AttachAsModified(currentlogin, Me.ChangeSet.GetOriginal(currentlogin))
    End Sub
    
    Public Sub DeleteLogin(ByVal login As login)
        If (login.EntityState = EntityState.Detached) Then
            Me.ObjectContext.logins.Attach(login)
        End If
        Me.ObjectContext.logins.DeleteObject(login)
    End Sub

    Public Function GetLoginsByName(ByVal username As String, ByVal password As String) As IQueryable(Of login)

        Return Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password)

    End Function

    Public Function countloginsmatching(ByVal username As String, ByVal password As String) As Integer
        'Return Me.ObjectContext.logins.Distinct().Count(Function(t) t.username = username AndAlso t.password = password)
        Dim credentials As List(Of login)
        credentials = Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password).ToList()
        Return credentials.Count
    End Function

End Class

and all the code for mainpage.xaml.vb:

Imports System.ServiceModel.DomainServices.Client
Imports EstimatorSilverlight.Web

Partial Public Class MainPage
    Inherits UserControl

    Public Sub New()
        InitializeComponent()
    End Sub

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnLogin.Click

        Dim username As String = tbUserName.Text
        Dim password As String = pbPassword.Password
        Dim totalcount As Integer = -1
        Dim context = New LoginContext()

        context.Load(context.GetLoginsByNameQuery(username, password))
        totalcount = context.countloginsmatching(username, password).Value
        Label3.Content = context.countloginsmatching(username, password).Value.ToString

        DataGrid1.ItemsSource = context.logins

    End Sub
End Class

[trevorjeaton]

tried the code changes and same thing - returns a zero - i even went as far as inserting "Return 1" just to see if the damn 1 would come across, but nope - its a zero.......

[trevorjeaton]

by the way, if you want to totally duplicate it, just create a database and drop in a table called login and add username and password as fields.....the project itself is called EstimatorSilverlight and is a silverlight 4 app in VS2010 with an entity dataset attaching to the aforementioned login table....i mean really, from my chair its not overly complicated stuff......i'm just at a complete loss.....or i'm missing something that is blatantly obvious and i'm too close to the problem to see it clearly, one of the two lol

not to mention that the datagrid populates absolutely perfectly, so the two sides are definitely talking to each other, just not on the record count.....

[trevorjeaton]

here's another thought.....is there something on the xaml side of things that i need to set to allow integer values to be passed back and forth? i'm reaching at straws at this point but just thought i'd throw it out there because when i added the datagridview to test if the data was actually coming across in the first place, one of the things i had to change in xaml to get it to work was to set the "AutoGenerateColumns" flag to 'True' - it was originally defaulted to 'False'...could it be the same thing with passing int values?

again, just throwin stuff out there at this point.....

[trevorjeaton]

I dug a little deeper and from what i'm reading, this may be an InvokeOperation that i'm trying to do, so i changed the buttonclick code to this:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnLogin.Click

        Dim username As String = tbUserName.Text
        Dim password As String = pbPassword.Password
        Dim context = New LoginContext()

        context.Load(context.GetLoginsByNameQuery(username, password))

        Dim invokeop As InvokeOperation(Of Integer)
        invokeop = context.countloginsmatching(username, password)
        Label3.Content = invokeop.Value

        DataGrid1.ItemsSource = context.logins

    End Sub

and the domainservice code to this:

<Invoke()> _
    Public Function countloginsmatching(ByVal username As String, ByVal password As String) As Integer
        Dim credentials As List(Of login)
        credentials = Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password).ToList()
        Return credentials.Count
    End Function

so now i'm calling the invoke, but still.....its handing back a zero - if i do a break after the function is called on the domainservice side, the value is correct at 1. If i insert the break on the client side, the value is zero.

[techgnome]

To be honest, this is out of my depths... for the life of me, I can't see why it would be failing.

-tg

[trevorjeaton]

thanks for all the help thus far, I'm going to stay on this until its resolved - its a war of attrition at this point :-) - i'll post the results as i (hopefully) find them.

[trevorjeaton]

GOT IT!!!! what a pain this one was......so, as it turns out, the obvious presents itself after you find the solution.

Basically when you make a call through a domain service to a server side database from a silverlight client (or any client for that matter) there is some time that's required for the request to be sent and the answer to be received - the client could be in the U.S., China, Australia, wherever.........

What we were doing this whole time was sending the request but trying to post the results before we received the answer.

What was needed was to insert an addhandler and a new sub calling the values when the request was completed. Now keep in mind i still have to do all the navigation code and whatnot from this point forward but at least now the server is giving me a '1' when a user is found and a '0' when they are not. Here's the entire batch of code on the client side:

Imports System.ServiceModel.DomainServices.Client
Imports EstimatorSilverlight.Web

Partial Public Class MainPage
    Inherits UserControl
    Dim context = New LoginContext()
    Dim invokeop As InvokeOperation(Of Integer)

    Public Sub New()
        InitializeComponent()
    End Sub

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnLogin.Click
        Dim username As String = tbUserName.Text
        Dim password As String = pbPassword.Password

        invokeop = context.countloginsmatching(username, password)
        AddHandler invokeop.Completed, AddressOf invokeOperation_Completed

    End Sub

    Private Sub invokeOperation_Completed(ByVal Sender As Object, ByVal E As EventArgs)
        Label3.Content = invokeop.Value
    End Sub

    Private Sub LoginDomainDataSource_LoadedData(ByVal sender As System.Object, ByVal e As System.Windows.Controls.LoadedDataEventArgs) Handles LoginDomainDataSource.LoadedData

        If e.HasError Then
            System.Windows.MessageBox.Show(e.Error.ToString, "Load Error", System.Windows.MessageBoxButton.OK)
            e.MarkErrorAsHandled()
        End If
    End Sub
End Class

So the key on this side was to keep persuing the Invoke tag on the domain services side and to use the addhandler to enumerate the results when the client received them. Point in case from earlier code is that i had to move my context declaration and my invoke operation to the main part of the load so that they were public for the rest of the form then add my Private Sub to match the Addhandler - in this case it was the Private Sub InvokeOperation_Completed code. I left the label3.content in there just to show the results, but have since cleared out the datagrid function and the code in completed operation will be replaced with something along the lines of

if invokeop.value=1
   'redirect to the main page of the app
else
  'fire a try again window up to three times, then block the ip address for 30 minutes
endif

and last but not least, here's all the code for the domainservice as well - the most important part of course is the final function and the Invoke tag attached to it:

Option Compare Binary
Option Infer On
Option Strict On
Option Explicit On

Imports EstimatorSilverlight.Web
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations
Imports System.Data
Imports System.Linq
Imports System.ServiceModel.DomainServices.EntityFramework
Imports System.ServiceModel.DomainServices.Hosting
Imports System.ServiceModel.DomainServices.Server

'Implements application logic using the LoginEntities context.
' TODO: Add your application logic to these methods or in additional methods.
' TODO: Wire up authentication (Windows/ASP.NET Forms) and uncomment the following to disable anonymous access
' Also consider adding roles to restrict access as appropriate.
'<RequiresAuthentication> _

<EnableClientAccess()>  _
Public Class LoginService
    Inherits LinqToEntitiesDomainService(Of LoginEntities)
    
    'TODO:
    ' Consider constraining the results of your query method.  If you need additional input you can
    ' add parameters to this method or create additional query methods with different names.
    'To support paging you will need to add ordering to the 'logins' query.

    <Query(IsDefault:=True)> _
    Public Function GetLogins() As IQueryable(Of login)
        Return From login In Me.ObjectContext.logins Order By login.username
    End Function
    
    Public Sub InsertLogin(ByVal login As login)
        If ((login.EntityState = EntityState.Detached)  _
                    = false) Then
            Me.ObjectContext.ObjectStateManager.ChangeObjectState(login, EntityState.Added)
        Else
            Me.ObjectContext.logins.AddObject(login)
        End If
    End Sub
    
    Public Sub UpdateLogin(ByVal currentlogin As login)
        Me.ObjectContext.logins.AttachAsModified(currentlogin, Me.ChangeSet.GetOriginal(currentlogin))
    End Sub
    
    Public Sub DeleteLogin(ByVal login As login)
        If (login.EntityState = EntityState.Detached) Then
            Me.ObjectContext.logins.Attach(login)
        End If
        Me.ObjectContext.logins.DeleteObject(login)
    End Sub

    <Invoke()> _
    Public Function countloginsmatching(ByVal username As String, ByVal password As String) As Integer
        Dim credentials As List(Of login)
        credentials = Me.ObjectContext.logins.Where(Function(t) t.username = username AndAlso t.password = password).ToList()
        Return credentials.Count
    End Function

End Class

special thanks to techgnome for comin along for the ride on this one - will also be updating my silverlight thread to complete the original post when i started this puppy.

thanks again TG

[techgnome]

Cool... glad you found the answer and thanks for sharing it. If you could, mark the thread resolved, - under Thread Tools at the top- ... that way some one else looking for a solution will know to look here.

-tg