How to display duplicate values and unique values in two different tables Entity Framework

Question

now I'll try describe my situation correctly: I have 2 models with same properties here is this models:

[Table("OperatorA")]
public class OperatorA
{
    [Key]
    public string Id { get; set; }
    public string Number { get; set; }
    public string CallTime { get; set; }
    public string Duration { get; set; }
    public decimal Cost { get; set; }
}

And

[Table("OperatorB")]
public class OperatorB
{
    [Key]
    public string Id { get; set; }
    public string Number { get; set; }
    public string CallTime { get; set; }
    public string Duration { get; set; }
    public decimal Cost { get; set; }
}

After that I have inserted data in this tables and everything works fine, but - now I need compare this two tables: 1. Check duplicate Number property in both table and view theme. and 2. View all Number values that is not duplicate. Can anyone help me with controller.

public async Task<ActionResult> Index()
    {
        var operatorA = await db.OperatorAs.ToListAsync();
        var operatorB = await db.OperatorBs.ToListAsync();

        // Want get all operatorB Numbers witch not equals OperatorA Numbers
    }

Diana · Accepted Answer · 2016-12-09 22:47:30Z

Be aware that a query like this can easily cause big performance problems, depending of the number of records in the lists. Getting the whole lists to your application server with await and ToListAsync and later filtering the results in your controller is usually not a good practice. Use it only if you are sure that your lists will not get longer than a few hundreds of records (as a raw estimation). Do some metrics of execution times and amount of data used by the operation.

The best solution would be to do the query filtering in the database server, with the joins and filters applied before doing the .ToList(), because when you materialize the resulting list of records the query is executed in the database, and any further filtering or processing would not be done in the database but in the application server.

This example query that uses a join between the two tables would get you the list of OperatorA elements with Number repeated in OperatorB:

var query = db.OperatorAs
    .Join<OperatorA, OperatorB, string, OperatorA>(
        db.OperatorBs,
        a => a.Number,
        b => b.Number,
        (a, b) => a)
   .ToList();

It generates this SQL:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Number] AS [Number], 
    [Extent1].[CallTime] AS [CallTime], 
    [Extent1].[Duration] AS [Duration], 
    [Extent1].[Cost] AS [Cost]
    FROM  [dbo].[OperatorAs] AS [Extent1]
    INNER JOIN [dbo].[OperatorBs] AS [Extent2] 
        ON ([Extent1].[Number] = [Extent2].[Number]) 
        OR (([Extent1].[Number] IS NULL) AND ([Extent2].[Number] IS NULL))

If the lists are really big you can also cause performance problems even in the database server, but database servers are better prepared to deal with these issues than application servers. There are ways to fix it as well.

Collectives™ on Stack Overflow

How to display duplicate values and unique values in two different tables Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related