MattCucco MattCucco - 6 months ago 16
Vb.net Question

Upgrading to Entity Framework

I am trying to recover the id to the last document created by a user within my program. The original SQL work is not my own, but I have been tasked with upgrading to EF. Code snippets below. Just need to know how to change the syntax so it works using EF instead. (Using VB.Net)

'query for the max item created by the user
SqlString = "SELECT max(IdDocuments) as MaxId FROM Documents WHERE ModifiedBy='" + Environment.UserName.ToLower + "' ;"
SqlDataAdapter = New SqlDataAdapter(SqlString, SqlConnectionString)
TableNow = New DataTable
SqlDataAdapter.Fill(TableNow)
SqlDataAdapter.SelectCommand.Connection.Close()`


What I have tried already:

DocNow = (From a In Db.Documents Where a.ModifiedBy = Environment.UserName.ToLower)


Connection to DB defined as:

'query the database
Dim IdNow As Integer = DocumentId
Dim DocNow As IEnumerable(Of Documents) = (From a In Db.Documents Where a.IdDocuments = IdNow).ToList


Here is Current code after help from bwyn (SQL included):

'if is new, get the last document for this user
If IsNew Then

'query for the max item created by the user
'SqlString = "SELECT max(IdDocuments) as MaxId FROM Documents WHERE ModifiedBy='" + Environment.UserName.ToLower + "' ;"
'SqlDataAdapter = New SqlDataAdapter(SqlString, SqlConnectionString)
'TableNow = New DataTable
'SqlDataAdapter.Fill(TableNow)
'SqlDataAdapter.SelectCommand.Connection.Close()


Dim context As New Context()
Dim lastId As Integer
Dim currentUser As String = Environment.UserName.ToLower()
lastId = context.Documents.Where(Function(doc) doc.ModifiedBy = currentUser).Select(Function(doc) doc.IdDocuments).Max()


'set to the document id
DocumentId = lastId

End If

Answer

This will query for the current user's documents, select the ids, and then return the max id:

    Dim context As New Context()
    Dim lastId As Integer
    Dim currentUser As String = Environment.UserName.ToLower()
    lastId = context.Documents.Where(Function(doc) doc.ModifiedBy = currentUser).Select(Function(doc) doc.Id).Max()

Edit: My context class

Public Class Context
    Inherits DbContext

    Public Property Documents As DbSet(Of Document)

End Class