Help with Linq AutoIncrement RRS feed

  • Question

  • I am working on an application, and have most of it finished. I am running into a problem with looking up the last record in an entity. The idea is to get the last record and then compare it to another one and then update it. I will explain what I am trying to do. 

    Part of the application requirement is to increment by one every time the record is created. Here is what I have done so far.

    I have a field called incident number and it is based on year and month and 000.  It also changes every month. For example November 2013 would be 311000. When a user creates a new incident on November 1st. the number would be 311001, and auto increment from there.  its now December and the number has to change to 312000 and then autoincrement by 1.  if the last incident number is greater than the current year format then it increments by 1. if it is not, then the current year format auto increments by 1.  So if the next number in the set is 312002 it will auto increment by 1.

    I have this so far.

    this is how I get the incident number first. 

    Dim CMY = Date.Now
            'Dim CMYIN = Date.Now.ToString("yM\000")
            'Dim  DTSRING= Right(CMYIN, 6)
            'INSTRING = DTSRING

    Then I need to get the last record in the table, which is the last incident number. So I thought of using either max or descending query. I tried a few linq querries but couldn't get it to work. I want to get the last record and check if it is greater than the current date time.  If the record is greater than it autoincrement by one. If the record is not greater than it starts over again. 

    what I need to do is this: 

    dim clastincident =

    if lastincident number > INSTRING then

    lastincidentnumber +1

    else instring +1

    Incidentnumber = clastincident

    I tried this

    Dim lastincidentnumber = From entity In Me.DataWorkspace.ApplicationData.HCICADDATA.OrderByDescending(Function(x) x.INCIDENT_NUMBER).Take(1)

    It gave me the following error:

    Unable to cast object of type 'Microsoft.LightSwitch.ClientGenerated.Implementation.QueryImplementation`1[LightSwitchApplication.HCICADDATA]' to type 'System.String'.

    Thank you for any help.


    Monday, December 2, 2013 6:44 PM


  • Hi Sean,

    Is "HCICADDATA" an entity in your project? The error message indicates that its type cannot match string.

    You may try the code below:

    Dim lastincidentnumber = From entity In Me.DataWorkspace.ApplicationData.HCICADDATA.ToList().OrderByDescending(Function(X) x.INCIDENT_NUMBER).Take(1) 

    You can add Tolist() behind HCICADDATA.

    Besides, you may get specialized help about linq from the links below according to your requirement.

    Linq to SQL


    Linq to Entities


    Hope it helps

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 4, 2013 3:35 AM