none
How do you return a single record? RRS feed

  • Question

  • Hello

    I have created a Silverlight Application using an ADO.NET Entity Data Model and a WCF service.

    Now when I want to return data from the database, I create Functions in the IService that is always a "List of" the datamodel.

    Then in the Service.svc I would write LINQ to Entities code like:

     

    Dim dc As New NorthwindEntities

    Dim items = From cust In

    dc.Customers

     

    Order By

    cust.CompanyName

     

    Select

    cust

     

    Return items.ToList()

    Which returns a list of Customers.

    But I want to return only ONE Customer.

    From what I have seen on the web it looks like I need to do that exact same set up and then use this LINQ to Entities code like:

     

     

     

    Dim dc As New NorthwindEntities

    Dim item = (From cust In dc.Customers

     

    Where cust.CustomerID = Entity.CustomerID

     

     

    My question is do I always set up with a "List OF" what ever the data model is. And then use "FirstOrDefualt" to get the single record?

    Is that the correct way to do it? Is there a way where I only query the database for just one record? To me is seems like a waste to return all the customers instead of just returning one record.

    I am new to Entity Framework so maybe I am totally wrong here but I would appreciate if someone could help me out here.

    Thank you

     
    Select cust).FirstOrDefault()

    Monday, January 30, 2012 5:15 AM

Answers

  • Hi mutlyp,

    In order to get a single element from a query you can use any of the following extension methods:

    1. .First() - Returns the first element in the sequence. If the sequence contains no elements an exception is thrown.
    2. .FirstOrDefault() - Returns the first element in the sequence. If the sequence contains no elements the default value for the type is returned (usually null, for int 0, for bool false, etc.).
    3. .Single() - Returns the only element in the sequence. If the sequence does not contain exactly 1 element an exception is thrown.
    4. .SingleOrDefault() - Returns the only element in the sequence. If the sequence does not contain exactly 1 element the default value for the type is returned.

    It is important to note that you shouldn't use .ToList().FirstOrDefault() on your LINQ 2 Entities queries since this will load into memory all objects returned from the query and then select the first one. It's more efficient to leave out the .ToList() and then you'll only get the 1 result from the database.

    Regards,

    Tyler

    • Marked as answer by mutlyp Tuesday, January 31, 2012 9:24 PM
    Monday, January 30, 2012 10:39 AM
  • Hi mutlyp,

    You need to change the signature of the method so that it returns a single entity rather than a list:

     

    Public Function GetLogIn(ByVal PW As String, ByVal UN As String) As surv_Employees Implements ILogInService.GetLogIn
    
    You'll also need to modify the signature of the interface ILogInService.GetLogIn to match the return type.

     

    Regards,

    Tyler


    • Edited by Tyler_A Tuesday, January 31, 2012 6:35 PM
    • Marked as answer by mutlyp Tuesday, January 31, 2012 9:24 PM
    Tuesday, January 31, 2012 6:34 PM

All replies

  • I think you could return IQueryable(Of Customer) from your query, so you can attach ToList() or FirstOrDefault() to it later.
    Monday, January 30, 2012 9:19 AM
  • Hi mutlyp,

    In order to get a single element from a query you can use any of the following extension methods:

    1. .First() - Returns the first element in the sequence. If the sequence contains no elements an exception is thrown.
    2. .FirstOrDefault() - Returns the first element in the sequence. If the sequence contains no elements the default value for the type is returned (usually null, for int 0, for bool false, etc.).
    3. .Single() - Returns the only element in the sequence. If the sequence does not contain exactly 1 element an exception is thrown.
    4. .SingleOrDefault() - Returns the only element in the sequence. If the sequence does not contain exactly 1 element the default value for the type is returned.

    It is important to note that you shouldn't use .ToList().FirstOrDefault() on your LINQ 2 Entities queries since this will load into memory all objects returned from the query and then select the first one. It's more efficient to leave out the .ToList() and then you'll only get the 1 result from the database.

    Regards,

    Tyler

    • Marked as answer by mutlyp Tuesday, January 31, 2012 9:24 PM
    Monday, January 30, 2012 10:39 AM
  • Thank you for the response.

    And forgive my ignorance but I want to make sure I got this correct.

    So I do have to always set up a Function with a "List OF" but when I do the LINQ to Entity I put in the .First, .FirstOrDefault.....

    My question is so when I do a "List Of" Customers and then on the LINQ to Entity I put in .SingleOrDefault

    Does the "List Of" return the entire list of Customers and then filters the .SingleOrDefault? Or does the .SingleOrDefault make it only return the Single Record?

     

    Again thanks for  the help

    Monday, January 30, 2012 4:53 PM
  • On 1/30/2012 11:53 AM, mutlyp wrote:
    > Thank you for the response.
    >
    > And forgive my ignorance but I want to make sure I got this correct.
    >
    > So I do have to always set up a Function with a "List OF" but when I do
    > the LINQ to Entity I put in the .First, .FirstOrDefault.....
    >
    > My question is so when I do a "List Of" Customers and then on the LINQ
    > to Entity I put in .SingleOrDefault
    >
    > Does the "List Of" return the entire list of Customers and then filters
    > the .SingleOrDefault? Or does the .SingleOrDefault make it only return
    > the Single Record?
    >
    > Again thanks for the help
    >
     
    var hit = (from a in db.Customers where a.ID == 92 select
    a).SingleOrDefault();
     
    If (hit != null)  // you have a returened single object
    {
      int id = hit.ID;
     
    {
     If hit is null, then no object was found to match the criteria.
     
    On that same token, you can use FirstOrDefault() it does the same thing.
     
    Monday, January 30, 2012 7:10 PM
  • As soon as you call .ToList() to return List(Of <Entity>), you gather the full list of elements qualifying your query. The .SingleOrDefault() returns the first element of the list, or Nothing if the list is empty.

    So to return only one item, just call item.SingleOrDefault(). (Your variable item itself is ObjectQuery type i.e. IQueryable, so it will not be executed when it's declared, and only got executed when you tell it to bring in data)

    • Edited by cheong00 Tuesday, January 31, 2012 1:15 AM
    Tuesday, January 31, 2012 1:12 AM
  • Thank you for the response.

    I changed

    Public Function GetLogIn(ByVal PW As String, ByVal UN As String) As List(Of surv_Employees) Implements ILogInService.GetLogIn

            Dim dc As New LogInsEntities

            Dim item = From login In dc.surv_Employees
                       Where login.PW = PW And login.U_Name = UN
                       Select login

            Return item.FirstOrDefault

        End Function

     

    TO

    Public Function GetLogIn(ByVal PW As String, ByVal UN As String) As List(Of surv_Employees) Implements ILogInService.GetLogIn

            Dim dc As New LogInsEntities

                   Dim item = (From login In dc.surv_Employees
                    Where login.PW = PW And login.U_Name = UN
                     Select login).FirstOrDefault

            Return item

        End Function

    But I get an error saying:

    Value of type Surveys.Web.surv_Employees cannot be conveted to systems.collections.generic.List(Of Surveys.Web.surv_Employees)

    Please tell me what I am doing wrong

    Thank you


    • Edited by mutlyp Tuesday, January 31, 2012 6:16 PM edited text
    Tuesday, January 31, 2012 6:16 PM
  • Hi mutlyp,

    You need to change the signature of the method so that it returns a single entity rather than a list:

     

    Public Function GetLogIn(ByVal PW As String, ByVal UN As String) As surv_Employees Implements ILogInService.GetLogIn
    
    You'll also need to modify the signature of the interface ILogInService.GetLogIn to match the return type.

     

    Regards,

    Tyler


    • Edited by Tyler_A Tuesday, January 31, 2012 6:35 PM
    • Marked as answer by mutlyp Tuesday, January 31, 2012 9:24 PM
    Tuesday, January 31, 2012 6:34 PM
  • Hi Tyler,

    Thank you for the help.

    so when I do:

    Public

     

     

    Function GetLogIn(ByVal PW As String, ByVal UN As String) As List(Of surv_Employees) Implements ILogInService.GetLogIn

    I am returning a list of surv_Employees

    But when I do this:

    Public

     

     

    Function GetLogIn(ByVal PW As String, ByVal UN As String) As surv_Employees Implements ILogInService.GetLogIn

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    I am returning a single entity of surv_Employees.

    Is that correct?

    Thank you again for the help.

    Tuesday, January 31, 2012 7:08 PM
  • Yes, that's correct.
    Tuesday, January 31, 2012 7:29 PM
  • Hi Tyler,

    Sorry for being such a pain. But I still need help.

     made those changes and got rid of those errors. Yea!

    But now when I run the app I do not get the data back.

    Here is my ILogInService:

    Imports System.ServiceModel

    <ServiceContract()>

    Public Interface ILogInService

    <OperationContract()>

    Function GetLogIn(ByVal PW As String, ByVal UN As String) As surv_Employees 

    End Interface

     

    Here is my LogInService.svc

    Imports System.Collections.Generic

    ' NOTE: You can use the "Rename" command on the context menu to change the class name "LogInService" in code, svc and config file together.
    Public Class LogInService
        Implements ILogInService

        Public Function GetLogIn(ByVal PW As String, ByVal UN As String) As surv_Employees Implements ILogInService.GetLogIn

            Dim dc As New LogInsEntities

            Dim item = (From login In dc.surv_Employees
                       Where login.PW = PW And login.U_Name = UN
                       Select login).FirstOrDefault

            Return item

        End Function

    End Class

    Here is my LogInViewModel.vb


    Imports System.Collections.ObjectModel
    Imports Surveys.LogInServiceReference
    Imports System.IO.IsolatedStorage

    Public Class LogInViewModel
        Inherits ViewModelBase

        Public Sub New()

        End Sub

        'using appSettings to store data locally for use throughout the session
        Private appSettings As IsolatedStorageSettings = IsolatedStorageSettings.ApplicationSettings

        'is list of employees
        Private _LogInCollection As ObservableCollection(Of surv_Employees)

        'One single employee
        Private _CurrentLogIn As surv_Employees


        Public Property CurrentLogIn() As surv_Employees
            Get
                Return _CurrentLogIn
            End Get
            Set(ByVal value As surv_Employees)
                _CurrentLogIn = value
                'Used to notify app the a property change has occured
                RaisePropertyChanged("CurrentLogIn")
            End Set
        End Property

     

        Public Property LogInCollection() As ObservableCollection(Of surv_Employees)
            Get
                Return _LogInCollection
            End Get
            Set(ByVal value As ObservableCollection(Of surv_Employees))
                _LogInCollection = value
                'Used to notify app the a property change has occured
                RaisePropertyChanged("LogInCollection")
            End Set
        End Property
        'property for loginserviceclient
        Private WithEvents LogInClient As New LogInServiceClient

        'Call to GetLogIn
        Public Sub GetLogIn(ByVal ID As String, ByVal UN As String)
            LogInClient = New LogInServiceClient

            LogInClient.GetLogInAsync(ID, UN)


        End Sub


        'Gets results from entity call
        Private Sub LogInClient_GetLogInCompleted(sender As Object, e As LogInServiceReference.GetLogInCompletedEventArgs) Handles LogInClient.GetLogInCompleted
            If e.Error Is Nothing Then
                LogInCollection = e.Result

                If e.Result.Count = 0 Then
                Else
                    'Grab the MyID value from query and put it in the appSetting so it can be used throughout the session
                    appSettings.Add("EmpID", e.Result.Single.EmpID)
                    appSettings.Add("MyID", e.Result.Single.EmpID)
                    appSettings.Add("F_Name", e.Result.Single.F_Name)
                    appSettings.Add("L_Name", e.Result.Single.L_Name)
                    appSettings.Add("PermissonID", e.Result.Single.PermissionID)
                    appSettings.Add("Email", e.Result.Single.Email)

                    LogInClient.CloseAsync()
                End If
            End If
        End Sub

        'property to show app that controls are enabled
        Private _IsEnabled As Boolean = True
        Public Property IsEnabled() As Boolean
            Get
                Return _IsEnabled
            End Get
            Set(ByVal value As Boolean)
                _IsEnabled = value
                RaisePropertyChanged("IsEnabled")

            End Set
        End Property

        'property to show app that controls are visible
        Private _IsVisible As Visibility = Visibility.Collapsed
        Public Property IsVisible() As Visibility
            Get
                Return _IsVisible
            End Get
            Set(ByVal value As Visibility)
                _IsVisible = value
                RaisePropertyChanged("IsVisible")
            End Set
        End Property

    End Class

    And here is my code behind:

    Partial Public Class LogIn
        Inherits UserControl

        Public Sub New
            InitializeComponent()

            _viewModel = DirectCast(Me.Resources("viewModel"), LogInViewModel)
        End Sub

        Private _viewModel As LogInViewModel

        Private Sub btnLoginSubmit_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnLoginSubmit.Click
          
            _viewModel.GetLogIn(Me.txtPW.Password, Me.txtUserName.Text)

        End Sub
    End Class

    ---------------------------------------------------------------------------------

    When I run the app I get to the LogInService.svc.vb

    Dim item = (From login In dc.surv_Employees
                       Where login.PW = PW And login.U_Name = UN
                       Select login).FirstOrDefault

            Return item

    And look at 'item' it shows the record I wanted to return. But when I continue the app it goes next to

    Private Sub LogInClient_GetLogInCompleted(sender As Object, e As LogInServiceReference.GetLogInCompletedEventArgs) Handles LogInClient.GetLogInCompleted
            If e.Error Is Nothing Then
                LogInCollection = e.Result

                If e.Result.Count = 0 Then
                Else
                    'Grab the MyID value from query and put it in the appSetting so it can be used throughout the session
                    appSettings.Add("EmpID", e.Result.Single.EmpID)
                    appSettings.Add("MyID", e.Result.Single.EmpID)
                    appSettings.Add("F_Name", e.Result.Single.F_Name)
                    appSettings.Add("L_Name", e.Result.Single.L_Name)
                    appSettings.Add("PermissonID", e.Result.Single.PermissionID)
                    appSettings.Add("Email", e.Result.Single.Email)

                    LogInClient.CloseAsync()

                End If

            End If
        End Sub
    And look at e.Result

    I get 'Error No Children Available' and count = 0

     

    again tell me what I am doing wrong.

     

    Thank you

     

    Tuesday, January 31, 2012 8:28 PM
  • I figured it out. I had to change the line in my LogInViewModel that read

    LogInCollection = e.Result

    to

    CurrentLogIn = e.Result

    that changed it from an Observable Collection to a single entity.

    everything worked after that.

    Thank you all for all your help.

    Tuesday, January 31, 2012 9:23 PM