Raw SQL queries with EF code-first doesn't work

Question

I'm trying to display values from 2 tables (EF code-first) using SQL JOIN and WHERE clause.

Dim query = db.pt.SqlQuery("SELECT p.id, p.name, p.dob, pv.visitId, pv.vDate
                             FROM pt p 
                             JOIN pt_v pv ON p.id = pv.id
                             WHERE p.id like @p0 OR p.name like @p1", str1, str2).ToList()

I've tried an alternate way. Below is the code:

Dim id = New SqlParameter("@id", str1)
Dim name = New SqlParameter("@name", str2)
Dim query As String = "select p.id id, p.name name, p.dob dob, pv.visitId visitId, pv.vDate vDate
                       from pt p 
                       join pt_v pv on p.id = pv.id
                       where p.id like @id or p.name like @name"

Dim Result = db.Database.SqlQuery(Of PtLookupVM)(query, id, name).ToList()

PtLookupVM is my viewmodel class that has these:

Public Property id As String
Public Property name As String
Public Property dob As String
Public Property visitId As String
Public Property vDate As String

But the output result for both is zero rows.

This is the sample data:

Dim p As New List(Of pt) From {
        New pt() With {.id = 1, .name = "siti", .dob = "2011-11-17"},
        New pt() With {.id = 2, .name = "siti nur", .dob = "2011-12-17"}
}

Dim pv As New List(Of pt_v) From {
        New pt_v() With {.visitId = 1, .id = 1, .vDate = "2016-01-17"},
        New pt_v() With {.visitId = 2, .id = 1, .vDate = "2016-05-17"},
        New pt_v() With {.visitId = 3, .id = 2, .vDate = "2016-07-17"}
}


Dim pList As New List(Of PtLookupVM) From {
        New PtLookupVM() With {.id = 1, .name = "siti", .dob = "2011-11-17", .visitId = 1, .vDate = "2016-01-17"},
        New PtLookupVM() With {.id = 1, .name = "siti", .dob = "2011-11-17", .visitId = 2, .vDate = "2016-05-17"},
        New PtLookupVM() With {.id = 2, .name = "siti nur", .dob = "2011-12-17", .visitId = 3, .vDate = "2016-07-17"}
    }

What i'm trying to get is actually a list of people with the name "siti". But i don't want redundant values. So it should only display values as below:

This is my expected output:

Dim pList As New List(Of PtLookupVM) From {
        New PtLookupVM() With {.id = 1, .name = "siti", .dob = "2011-11-17", .visitId = 1, .vDate = "2016-01-17"},
        New PtLookupVM() With {.id = 2, .name = "siti nur", .dob = "2011-12-17", .visitId = 3, .vDate = "2016-07-17"}
}

Answer 1

Let take the query with named parameters (what you call the alternate way). The criteria used in WHERE clause referring to these parameters are

p.id like @id

and

p.name like @name

As you can see, there are no placeholders used, so if you don't supply them thru parameters, the LIKE will simply work as =, thus will not match what do you expect when for instance str2 contains "sity".

To match the string regardless of the position inside the target field, you should surround the user value with "%":

Dim id = New SqlParameter("@id", "%" & str1 & "%")
Dim name = New SqlParameter("@name", "%" & str2 & "%")

or if you prefer the first variant with implicit parameters:

Dim query = db.pt.SqlQuery("SELECT p.id, p.name, p.dob, pv.visitId, pv.vDate
                             FROM pt p 
                             JOIN pt_v pv ON p.id = pv.id
                             WHERE p.id like @p0 OR p.name like @p1", "%" & str1 & "%", "%" & str2 & "%").ToList()