CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6ef-code-first
juFo
juFo

Reputation: 18587

C# Entity Framework / SQL Server count number of matched data

Given a table Foo:

table Foo (
  name,
  city,
  country,
  year,
  color,
  age
);

Data that is stored in the Foo table:

Peter, Brussels, Belgium, 2013, blue, 19
Dirk, Brussels, Belgium, 1999, red, 33
Jean, Paris, France, 2011, blue, 27

Line of data I want to match against database:

Peter, Brussels, Belgium, 2013, blue, 19

==> should give for:

row 1: 100% or 6
row 2: 33% or 2
row 3: 16% or 1

Another line of data to test:

Peter, Paris, France, 2011, green, 36

==> should give for:

row 1: 16% or 1
row 2: 0% or 0
row 3: 50% or 3

I could fetch all data in memory and run some C# logic on it, but that would not be the smartest thing.

Would it be possible to do this kind of calculation ("% that matches row data") in a single query?

Bonus:

Is it possible to add weights to columns so that column 3 and 5 would add a higher count-weight?

Using C# with EF with a code-first approach, and a SQL Server database (stored procedure is not an option as the database could be a different type).

Upvotes: 0

Views: 90

Answers (1)

stovroz
stovroz

Reputation: 7095

Use a ternary conditional operator for each condition which returns 1 or 0, and add each result. You could replace the 1s with other numbers to achieve weighting, in which case the value 6.0 in the percentage calculation should be replaced with the sum of the weight values.

 foos.Select(x => 
     (x.name == "Peter" ? 1 : 0) +
     (x.city == "Brussels" ? 1 : 0) +
     (x.country == "Belgium" ? 1 : 0) +
     (x.year == 2013 ? 1 : 0) +
     (x.color == "blue" ? 1 : 0) +
     (x.age == 19 ? 1 : 0))
     .Select(x => $"{Math.Round(x / 6.0 * 100, 0, MidpointRounding.AwayFromZero)}% or {x}");

Upvotes: 1

Related Questions