How do you return a single record?

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()

Answer 1

I think you could return IQueryable(Of Customer) from your query, so you can attach ToList() or FirstOrDefault() to it later.

Answer 2

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

Answer 3

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

Answer 4

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.

 

Answer 5

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)

Answer 6

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

Answer 7

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

Answer 8

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.

Answer 9

Yes, that's correct.

Answer 10

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

 

Answer 11

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.