Whether to support, object association entity table

[Harbor77]

Whether to support, object association entity table If it is sql: I will use Exists to associate temporary tables or table variables Temporary tables:

SELECT DocNum, Client FROM TableA
WHERE EXISTS(SELECT 1 FROM #Temp WHERE #Temp.A1 = TableA .A1 AND #Temp.A2 = TableA .A2 )

Table variable:

SELECT DocNum, Client FROM TableA
WHERE EXISTS(SELECT 1 FROM @temptable WHERE @temptable .A1 = TableA .A1 AND @temptable .A2 = TableA .A2 )

In the development process, after I converted Json to List, how to associate the entity table to query the database to get the result I want, like the above Sql statement, I don't know how to operate it more conveniently in Linq. I know that it can be achieved in a loop, one by one, but the efficiency is too low.

EF Core version: 5.0 Database provider: SqlServer Target framework: .NET 5.0 Operating system: Win10 IDE: Visual Studio 2019 16.11

[ajcvickers]

@77Harbor Can you explain a bit more about what your goal is here? What do your entity types look like? Can you describe in words what you are hoping to get back from the query?

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[Harbor77]

@77Harbor你能解释一下你在这里的目标是什么吗？你的实体类型是什么样的？你能用文字描述你希望从查询中得到什么吗？

Sorry, there is no reply due to some reasons. The goal I want is actually very simple, which is to query the structure set in the database, and then use the obtained data set to correlate to the database for query. For example, my entity structure

public class Goods
{
    public string GoodsCode { get; set;}
    public string GoodsName { get; set;}
    public string Price { get; set;}
    public string Years { get; set;}
}

public class GoodsColor
{
    public string GoodsCode { get; set;}
    public string ColorCode { get; set;}
}

public class GoodsSize
{
    public string GoodsCode { get; set;}
    public string Size { get; set;}
}

Now when I have some data in my spreadsheet to verify the color and specification of goods, I can only use cycles to verify, for example

List goodsList = new List();
goodsList.Add(new { GoodsCode = "8001", ColorCode = "332", Size = "XS" });
goodsList.Add(new { GoodsCode = "8001", ColorCode = "332", Size = "S" });
goodsList.Add(new { GoodsCode = "8001", ColorCode = "332", Size = "M" });
goodsList.Add(new { GoodsCode = "8001", ColorCode = "332", Size = "L" });
goodsList.Add(new { GoodsCode = "9003", ColorCode = "587", Size = "XS" });
goodsList.Add(new { GoodsCode = "9003", ColorCode = "587", Size = "S" });
goodsList.Add(new { GoodsCode = "9003", ColorCode = "587", Size = "M" });
goodsList.Add(new { GoodsCode = "9003", ColorCode = "587", Size = "L" });

foreach (var good in goodsList)
{
    var result = (from color in context.Set()
        join size in context.Set() on color.GoodsCode equals size.GoodsCode
        where color.GoodsCode = good.GoodsCode && color.ColorCode = good.ColorCode
            && size.Size = good.Size
        select new { color.GoodsCode }).FirstOrDefault();

    if (string.IsNullOrWhiteSpace(result))
    {
        // Processing failed
    }
}

For example, I would write like this in a stored procedure.

CREATE PROCEDURE dbo.TestPorc
@temptable Temp readonly --User-defined table variables
AS
BEGIN
declare @count int
declare @dbcount int

SELECT C.* INTO #dbtempTable FROM GoodsColor AS A
INNER JOIN GoodsSize AS B ON A.GoodsCode = B.GoodsCode
INNER JOIN @tempTable AS C ON A.GoodsCode = C.GoodsCode AND A.ColorCode = C.ColorCode AND B.Size = C.Size

SET @count = (SELECT COUNT(1) FROM @tempTable)
SET @dbcount = (SELECT COUNT(1) FROM #dbtempTable)
IF @count <> @dbcount
BEGIN
     --Processing failed
END
END

In the process of using EF Core, I have encountered many such problems. When writing stored procedures, temporary tables are used to correlate the database table structure to query. The same problem is encountered in the work of writing query reports. Many data sources are converted to temporary tables after calculating the association, and then use the temporary table to associate to get the final data set. But using EF Core in C#, I can only query and get an array of IDs. Use Contains to query the performance efficiency is very slow, or get an IQueryable and then go to the query results to associate the performance and efficiency is also very slow.

thanks

[smitpatel]

I am not sure what you intend to do with stored procedure if are planning to handle processing failed on client side, but ignoring the sproc written above, there are few options.

1. Do processing one at a time like above since the predicate is on 3 different values (GoodsCode, ColorCode, Size), contains wouldn't be useful and make things worse.
2. If the computation of joins is an issue then you can create a view with joins and then you would be querying only view rather than generating join (again one at a time processing).
3. Similar to (2), you can also write a sproc which takes 3 values as argument and get result of it. You should be able to query directly to database at that point to get single value result from the server.
4. If you want to avoid one a time processing then you need to transfer the client side data (goodsList from spreadsheet) to server side. You can create another table in database and load the data there. Then predicate will become a join in the query as a way to filter. This will be probably most efficient way to verify. In future it could be possible to load client side data to server using TVP. See #13239, #19016, #17031