CODEWITHSUNDEEP
sql-serverentity-frameworklinq
ibrahim
ibrahim

Reputation: 205

Entity Framework LINQ query

I would like to know how to change this entity framework linq query.

I have tableA with the following columns: project (varchar), code (varchar), assess (integer)

the codes data are hierarchical -- i.e 10011, 10012, 10013 ..etc are children of code 1001. Another example 123001, 123002, 12300, 12301 are children of code 1230.

I want query based on project and then go through every rows of code and retrieve all it children with assessment = 0 (for example)

I wrote the below code but it is not giving the correct result:

var result = db.tableA.AsQueryable(); 
var tempResult = result.Where(p => p.project.Equals(ProjectValue));
result = tempResult.Where(c => c.Code.StartsWith(c.Code) && c.assess== 0);

The query above is not returning the children of the codes where project equal 'ProjectValue'

Upvotes: 1

Views: 64

Answers (2)

Liviu Boboia
Liviu Boboia

Reputation: 1754

You need to know which records are the parents in order to do this, if not your condition StartsWith will always be true since you have the children and the parent in tempResult.

I'll assume that the codes with the minimum length are the parents. After you select the parents you just take all the records that start with the parent code but have different lengths than the parent codes.

var result = db.tableA.AsQueryable();
var minLength = result.Select(t => t.Code.Length).Min(); 
var tempResult = result.Where(p => p.project.Equals(ProjectValue) && p.Code.Length == minLength);
result = result.Where(c => tempResult.Any(d => c.Code.StartsWith(d.Code)) && c.assess== 0 && c.project.Equals(ProjectValue) && c.Code.Length != minLength);

Upvotes: 1

Armin
Armin

Reputation: 232

The closest thing I can think of right now, with the amount of context you've given in your question will end up being:

var result = db.tableA.AsQueryable(); 
var tempResult = result.Where(p => p.project.Equals(ProjectValue)).select(p => p.code);
result = result.Where(c => tempResult.contains(c.Code.substring(0, 4)) && c.assess== 0 && c.project.Equals(ProjectValue));

Upvotes: 0

Related Questions