Linq to SQL查询报错：如何获取SiteId在multipleSites列表中的站点？

Hey there! Let's break down this issue you're facing with Linq to SQL cross-schema IN queries and that annoying Argument 1: cannot convert from 'int' to 'DAL.CUsersSite' error.

错误原因分析 #

That error pops up because you probably tried comparing an int type SiteId directly with a DAL.CUsersSite entity object somewhere in your query—either in a join condition or a where clause. Linq to SQL can't implicitly convert an integer to an entire entity class, hence the conversion failure.

正确的查询写法 #

First, make sure your entity classes are properly mapped to their respective schemas. For example, if CUsersSite is in a non-default schema, add the Schema parameter to its Table attribute:

[Table(Name = "CUsersSite", Schema = "YourOtherSchema")]
public class CUsersSite
{
    [Column(IsPrimaryKey = true)]
    public int SiteId { get; set; }
    // other properties...
}

Then pick the right query based on what your multipleSites collection is:

场景1：multipleSites是int类型的SiteId列表 #

If multipleSites is a List<int> or similar collection of SiteIds, you can directly use the Contains method—Linq to SQL will translate this to a proper IN clause, even across schemas:

// Assuming your DataContext instance is named db
var targetSites = from site in db.Sites
                  where multipleSites.Contains(site.SiteId)
                  select site;

If you need to explicitly join with the CUsersSite table (to validate the SiteId exists there), make sure your join condition uses matching int types:

var targetSites = from site in db.Sites
                  join userSite in db.CUsersSites 
                    on site.SiteId equals userSite.SiteId
                  where multipleSites.Contains(userSite.SiteId)
                  select site;

场景2：multipleSites是DAL.CUsersSite实体集合 #

If multipleSites is a collection of CUsersSite objects, first extract the SiteIds into an integer list before using Contains:

var siteIds = multipleSites.Select(us => us.SiteId).ToList();
var targetSites = from site in db.Sites
                  where siteIds.Contains(site.SiteId)
                  select site;

错误解决方法总结 #

• Fix type mismatches: Always ensure the values you're comparing in join/where clauses are the same type (e.g., compare int SiteIds to other int SiteIds, not to entire entity objects).
• Extract scalar values from entities: If working with a collection of entities, pull out the specific integer SiteId property first to avoid type conflicts.
• Validate schema mappings: Double-check that your cross-schema entities have the Schema parameter set in their Table attribute—this ensures Linq to SQL targets the correct database schema.

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