- Notifications
You must be signed in to change notification settings - Fork 11
Why can't I use my function in a database query
Anyone using EF or some other ORM to query a database inevitably runs into this problem (or some variant of it).
Let's say I have a Person class:
Class Person Property Id As Integer Property LastName As String Property FirstName As String Property DateOfBirth As Date End Classand I want to write a query against the database, using a DB context, which returns all the people whose last name starts with "A":
Class MyDbContext Inherits DbContext Property Persons As DbSet(Of Person) Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder) optionsBuilder.UseSqlite("DataSource=People.db") End Sub End Class Dim ctx = New MyDbContext Dim qry = From person In ctx.Person Where person.LastName.StartsWith("A") Select person For Each person In qry Dim output = $"LastName: {person.LastName}, FirstName: {person.FirstName}, DateOfBirth: {person.DateOfBirth}" Console.WriteLine(output) NextEverything works fine.
But now, I want to write a query that returns all the peeople whose year of birth ends with "9" -- 2019, 2009, 1999 etc. There is no EndsWith method on Date, or even on Integer which is the type returned from the Date.Year property.
However, there is an EndsWith method on String. The simplest method of converting an Integer to String is by calling the ToString method.
Dim ctx1 = New MyDbContext Dim qry1 = From person In ctx.Person Where person.DateOfBirth.Year.ToString.EndsWith("9") Select person For Each person In qry1 Dim output = $"LastName: {person.LastName}, FirstName: {person.FirstName}, DateOfBirth: {person.DateOfBirth}" Console.WriteLine(output) NextBut this code fails, with some error about
TODO -- error
Even stranger, if I run what appears to be the exact same code against a List(Of Person):
Dim lst = New List(Of Person) ' fill list here Dim qry1 = From person In lst Where person.DateOfBirth.Year.ToString.EndsWith("9") Select person For Each person In qry1 Dim output = $"LastName: {person.LastName}, FirstName: {person.FirstName}, DateOfBirth: {person.DateOfBirth}" Console.WriteLine(output) NextThe code doesn't choke on .ToString, and runs without errors.
LINQ queries desugar to LINQ operator methods
question -- how does SQL Server understand StartsWith, EndsWith