Linq to SQL - How to verify a record doesn't exists before inserting

Question

I'm working on a little side app for a client whereby they provide me with a list of cities and I have to insert them into the database and associate them with their parent records.

Example:

ID   |   PID   |   Region
1         0         California
2         1         Los Angeles
3         1         San Fransisco

Now my code looks like this

    Dim input As StreamReader
    Dim index As Integer
    Dim filename As String
    Dim RegionDC As New DAL.RegionsDataContext



    For Each TextFile As String In Directory.GetFiles(Server.MapPath("~/app_data/business-trader cities/"))
        input = File.OpenText(TextFile)
        filename = New FileInfo(TextFile).Name
        index = 0


        'this is where we want to select the ID for the filename'
        Dim _ID = (From R In RegionDC.bt_Regions _
                 Where R.Region = filename.Replace(".txt", "") _
                 Select R.ID).FirstOrDefault

        While Not input.EndOfStream

            Dim q = (From r In RegionDC.bt_Regions _
                     Where r.Region = input.ReadLine() _
                     Select r.ID).FirstOrDefault

            '***********************************'
            ' HERE IS WHERE IM TRYING TO VERIFY'
            ' IF THE RECORD EXISTS OR NOT'
            '***********************************'

            'now we loop through the txt file'
            'and insert the data into the database'
            Dim oRegion As New DAL.bt_Region
            oRegion.Region = input.ReadLine()
            oRegion.FSSearchCount = 0
            oRegion.WSearchCount = 0
            oRegion.PID = _ID

            RegionDC.bt_Regions.InsertOnSubmit(oRegion)
            RegionDC.SubmitChanges()

        End While

        'clean up the locked files'
        input.Close()
        input.Dispose()
    Next

So basically if Los Angeles is in the TXT file, I don't want it re-entered into the database since it already exists.

Can anyone help me figure out how to verify if a record already exists prior to inserting?

Answer 1

Assuming that no record can have ID of 0, I think it's just a matter of testing whether q = 0.

But I think you have a bug in the code: you call input.ReadLine() twice in each iteration, so you test whether Los Angeles is already in the database and then try to insert San Francisco. Just capture the line in a variable and then use that.

Answer 2

LINQ To SQL Samples - EXISTS/IN/ANY/ALL

Public Sub LinqToSqlExists01()
    Dim q = From c In db.Customers _
            Where Not c.Orders.Any()

    ObjectDumper.Write(q)
End Sub

Answer 3

if (RegionDC.bt_Regions.Any(r => r.Region == input.ReadLine()))
{
    // Yes it exists
}
else
{
    // No it doesn't exist
}

Used a converter to make the VB version :)

If RegionDC.bt_Regions.Any(Function(r) r.Region = input.ReadLine()) Then
    ' Yes it exists 
Else
    ' No it doesn't exist 
End If