Answered by:
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 item = (From cust In dc.Customers
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:
- .First() - Returns the first element in the sequence. If the sequence contains no elements an exception is thrown.
- .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.).
- .Single() - Returns the only element in the sequence. If the sequence does not contain exactly 1 element an exception is thrown.
- .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
Regards,
Tyler
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:
- .First() - Returns the first element in the sequence. If the sequence contains no elements an exception is thrown.
- .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.).
- .Single() - Returns the only element in the sequence. If the sequence does not contain exactly 1 element an exception is thrown.
- .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 selecta).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 loginReturn 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).FirstOrDefaultReturn 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
Regards,
Tyler
Tuesday, January 31, 2012 6:34 PM -
Hi Tyler,
Thank you for the help.
so when I do:
Public
I am returning a list of surv_Employees
But when I do this:
Public
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 ILogInServicePublic 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).FirstOrDefaultReturn item
End Function
End Class
Here is my LogInViewModel.vb
Imports System.Collections.ObjectModel
Imports Surveys.LogInServiceReference
Imports System.IO.IsolatedStoragePublic Class LogInViewModel
Inherits ViewModelBasePublic 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 PropertyPublic 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 LogInServiceClientLogInClient.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.ResultIf 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 PropertyEnd Class
And here is my code behind:
Partial Public Class LogIn
Inherits UserControlPublic Sub New
InitializeComponent()_viewModel = DirectCast(Me.Resources("viewModel"), LogInViewModel)
End SubPrivate _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).FirstOrDefaultReturn 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.ResultIf 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.ResultI 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