Upgrading to Entity Framework

Question

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

1) Since in general SQL is not case sensitive, you shouldn't need to call `ToLower`. 2) Are there any issues with this code? — Zev Spitz, Jun 01 '16 at 14:46
No this code works as of now, but i have reformatted most of the code to not use data tables and rows, and instead use ienumerables. — MattCucco, Jun 01 '16 at 14:48
http://stackoverflow.com/questions/157786/how-do-i-get-the-max-row-with-a-group-by-in-linq-query — Steve Greene, Jun 01 '16 at 15:01

poppertech · Accepted Answer · 2016-06-01T15:30:18.250

0

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

edited Jun 01 '16 at 15:30

answered Jun 01 '16 at 15:13

poppertech

1,286
2
9
17

I think this will work, but what 'using' should i add? – MattCucco Jun 01 '16 at 15:16
Using context As New Context() – poppertech Jun 01 '16 at 15:23
I'm sorry I meant for the import. When I implemented your code it gave me an error on the "Dim context as New Context()" . When I use the intellisense, any of the imports it suggest do not fix the error. – MattCucco Jun 01 '16 at 15:28
I used the following import statement: Imports System.Data.Entity. I will post my Context class in my answer. – poppertech Jun 01 '16 at 15:29
Okay this should work. Unfortunately I can't test it for a while as i still have about 170 other errors to fix, but I will come back then and confirm for sure! – MattCucco Jun 01 '16 at 15:43
I have no idea how to do any of that but thanks! I'm just a co-op programmer so I'll have my boss look at it tomorrow since he wrote the original code and is the one who is teaching me entity. – MattCucco Jun 01 '16 at 15:47

Upgrading to Entity Framework

1 Answers1