求助：无法将含子查询的跨两表SQL查询转换为LINQ查询

数据老陈

2026-5-29

Fixing Your LINQ Conversion for the SQL IN Subquery

Hey there! Let's sort out your LINQ conversion issue. First, let's recap what your original SQL is doing: it's pulling all records from Tbl_Vulpith_Registration where Profile_Pic isn't null and the MemId exists in the set of MemIds from Tbl_List_Services.

What's Wrong with Your Current Code?

The key mistake is in the final Where clause:

.Where(c => c.MemId.Contains(listmemsmemIds))

You've got the Contains logic backwards! MemId is a single value (like an int or string), and listmemsmemIds is a collection. You need to check if the single MemId is inside the collection, not the other way around.

Correct LINQ Implementations

Option 1: Fix Your Step-by-Step Approach

This keeps your original structure but fixes the Contains order:

// Get registration records where Profile_Pic isn't null
var reglist = objentity.Tbl_Vulpith_Registration.Where(a => a.Profile_Pic != null);
// Extract all MemIds from the services table
var serviceMemIds = objentity.Tbl_List_Services.Select(b => b.MemId).ToList();
// Filter registrations where MemId is in the service MemId collection
var finalist = reglist.Where(c => serviceMemIds.Contains(c.MemId)).ToList();

Option 2: Concise Chained Syntax

If you prefer a more compact approach, you can combine all steps into one query (this mirrors your original SQL's subquery logic closely):

var finalist = objentity.Tbl_Vulpith_Registration
    .Where(a => a.Profile_Pic != null 
              && objentity.Tbl_List_Services.Select(b => b.MemId).Contains(a.MemId))
    .ToList();

Option 3: Use a JOIN (Often More Efficient)

Instead of using an IN subquery, you can use a JOIN to link the two tables directly. Entity Framework will typically translate this to a SQL JOIN which can perform better for large datasets:

// Query syntax
var finalist = from reg in objentity.Tbl_Vulpith_Registration
               join service in objentity.Tbl_List_Services 
                   on reg.MemId equals service.MemId
               where reg.Profile_Pic != null
               select reg;

// Method syntax equivalent
var finalist = objentity.Tbl_Vulpith_Registration
    .Join(objentity.Tbl_List_Services,
          reg => reg.MemId,
          service => service.MemId,
          (reg, service) => reg)
    .Where(reg => reg.Profile_Pic != null)
    .ToList();

All of these approaches will produce the same result as your original SQL query—pick the one that fits your coding style and performance needs best!

内容的提问来源于stack exchange，提问作者Mahendra R Varma