如何在LINQ中拆分多整数值列并筛选字符串列整数值及报错处理

Hey there! Let's break down why your current code is failing and how to fix it, plus cover the right ways to split that comma-separated ID column in LINQ.

Why your code isn't working #

The code you wrote works totally fine for in-memory collections (like a List<Banner>), but if you're using Entity Framework or LINQ to SQL to query a database, it breaks. Here's why: ORMs like EF need to translate your LINQ expressions into SQL that the database can run. Methods like Split() and Convert.ToInt32() don't have direct SQL equivalents, so EF can't convert them—hence the execution error you're seeing.

Fixes you can use #

Option 1: Load data to memory first (good for small datasets) #

If your Banners dataset isn't huge, pull all the records into memory first, then apply your original logic. This works because once data is in memory, you're using LINQ to Objects which supports all those .NET methods:

Banners = Banners.ToList() ' Fetch all records to memory first
                 .Where(Function(x) Not String.IsNullOrEmpty(x.IDs) AndAlso 
                        x.IDs.Split(New Char() {","c}, StringSplitOptions.RemoveEmptyEntries)
                              .Select(Function(a) Convert.ToInt32(a))
                              .Contains(3))
                 .ToList()

Just keep in mind: this isn't great for large datasets, since you'll load everything into memory at once.

Option 2: Database-friendly filtering (recommended for large datasets) #

To make EF translate your query to SQL correctly, use string matching that works with database LIKE functions. You need to handle edge cases to avoid false matches (like catching "13" when you're looking for "3"):

Dim targetId As String = "3"
Banners = Banners.Where(Function(x) 
                          Not String.IsNullOrEmpty(x.IDs) AndAlso 
                          (x.IDs = targetId OrElse 
                           x.IDs.StartsWith(targetId & ",") OrElse 
                           x.IDs.EndsWith("," & targetId) OrElse 
                           x.IDs.Contains("," & targetId & ","))
                        ).ToList()

This gets converted to SQL LIKE clauses that run directly on the database, which is way more efficient for big datasets.

If you're using EF Core 5 or later, you can use EF.Functions.Like for clearer code:

Dim targetId As String = "3"
Banners = Banners.Where(Function(x) 
                          Not String.IsNullOrEmpty(x.IDs) AndAlso 
                          (x.IDs = targetId OrElse 
                           EF.Functions.Like(x.IDs, $"{targetId},%") OrElse 
                           EF.Functions.Like(x.IDs, $"%{targetId},") OrElse 
                           EF.Functions.Like(x.IDs, $"%{targetId}%"))
                        ).ToList()

Don't skip those edge case checks though—they're crucial to avoid matching partial IDs.

Splitting that comma-separated ID column in LINQ #

• For in-memory data: Your original split logic works perfectly. Here's how to get a list of integers from the column:

  Dim bannerIds As List(Of Integer) = banner.IDs.Split(New Char() {","c}, StringSplitOptions.RemoveEmptyEntries)
                                                .Select(Function(s) Convert.ToInt32(s))
                                                .ToList()
  

• For database queries: You can't use Split() directly in the LINQ expression (as you found out). Instead:
  1. Load the data to memory first (small datasets) then split, or
  2. If you can modify your database, create a computed column or view to split the IDs—but that's more involved.

A quick design tip #

Storing multiple IDs in a single string column violates database normalization rules (first normal form), which makes queries slower and harder to maintain. If you can, consider creating a separate join table (like BannerAssociatedIds) where each row links a banner to one of its IDs. Then your query becomes way simpler and more efficient:

Banners = Banners.Where(Function(b) b.AssociatedIds.Any(Function(bi) bi.Id = 3)).ToList()

内容的提问来源于stack exchange，提问作者ArunkumaarCN