Linq search database considering duplicates

I've got the problem with searching database through duplicate list values.

First I search all occurrences by given string.

var parentIdList =  await _context.ECATEGORIES

            .Where(a => a.NAME.ToLower().Contains(partOfName.ToLower()))

            .ToListAsync(ct);

then

I retrieve all names when given PARENTID of parentIdList equals database ID

 var mainName = await _context.ECATEGORIES

                .Where(a=> parentIdList.Any(p=>p.PARENTID==a.ID) )

                .Select(s => s.NAME)

                .ToListAsync(ct);

My problem is that, sometimes property PARENTID is duplicated.

For example PARENTID = {1,1,2,2,4,5,6}

then result is mainName = {"a","b","c","d","e"}

But I want mainName = {"a", "a","b","b","c","d","e"}

asked Nov 19 '18 at 12:59

janek9971

114

Do you want to de-duplicate the ParentIds? I don't understand your train of thought and duplicate ids never seem to be a good idea.
– Marco
Nov 19 '18 at 13:04

This is not my DB but by coworker and ParentId can be duplicated knowingly.
– janek9971
Nov 19 '18 at 13:15

So you purposefully want duplicated ids as a result, yes?
– Marco
Nov 19 '18 at 13:17

My DB have unique PK ID, and parentid's which reference to PK ID. ParentId's can be duplicated and be null. Don't ask my why is that because it's not the case of my problem.
– janek9971
Nov 19 '18 at 13:19

add a comment |

I've got the problem with searching database through duplicate list values.

First I search all occurrences by given string.

var parentIdList =  await _context.ECATEGORIES

            .Where(a => a.NAME.ToLower().Contains(partOfName.ToLower()))

            .ToListAsync(ct);

then

I retrieve all names when given PARENTID of parentIdList equals database ID

 var mainName = await _context.ECATEGORIES

                .Where(a=> parentIdList.Any(p=>p.PARENTID==a.ID) )

                .Select(s => s.NAME)

                .ToListAsync(ct);

My problem is that, sometimes property PARENTID is duplicated.

For example PARENTID = {1,1,2,2,4,5,6}

then result is mainName = {"a","b","c","d","e"}

But I want mainName = {"a", "a","b","b","c","d","e"}

asked Nov 19 '18 at 12:59

janek9971

114

Do you want to de-duplicate the ParentIds? I don't understand your train of thought and duplicate ids never seem to be a good idea.
– Marco
Nov 19 '18 at 13:04

This is not my DB but by coworker and ParentId can be duplicated knowingly.
– janek9971
Nov 19 '18 at 13:15

So you purposefully want duplicated ids as a result, yes?
– Marco
Nov 19 '18 at 13:17

My DB have unique PK ID, and parentid's which reference to PK ID. ParentId's can be duplicated and be null. Don't ask my why is that because it's not the case of my problem.
– janek9971
Nov 19 '18 at 13:19

add a comment |

I've got the problem with searching database through duplicate list values.

First I search all occurrences by given string.

var parentIdList =  await _context.ECATEGORIES

            .Where(a => a.NAME.ToLower().Contains(partOfName.ToLower()))

            .ToListAsync(ct);

then

I retrieve all names when given PARENTID of parentIdList equals database ID

 var mainName = await _context.ECATEGORIES

                .Where(a=> parentIdList.Any(p=>p.PARENTID==a.ID) )

                .Select(s => s.NAME)

                .ToListAsync(ct);

My problem is that, sometimes property PARENTID is duplicated.

For example PARENTID = {1,1,2,2,4,5,6}

then result is mainName = {"a","b","c","d","e"}

But I want mainName = {"a", "a","b","b","c","d","e"}

asked Nov 19 '18 at 12:59

janek9971

114

I've got the problem with searching database through duplicate list values.

First I search all occurrences by given string.

var parentIdList =  await _context.ECATEGORIES

            .Where(a => a.NAME.ToLower().Contains(partOfName.ToLower()))

            .ToListAsync(ct);

then

I retrieve all names when given PARENTID of parentIdList equals database ID

 var mainName = await _context.ECATEGORIES

                .Where(a=> parentIdList.Any(p=>p.PARENTID==a.ID) )

                .Select(s => s.NAME)

                .ToListAsync(ct);

My problem is that, sometimes property PARENTID is duplicated.

For example PARENTID = {1,1,2,2,4,5,6}

then result is mainName = {"a","b","c","d","e"}

But I want mainName = {"a", "a","b","b","c","d","e"}

database linq any

asked Nov 19 '18 at 12:59

janek9971

114

asked Nov 19 '18 at 12:59

janek9971

114

asked Nov 19 '18 at 12:59

janek9971

114

asked Nov 19 '18 at 12:59

janek9971

114

asked Nov 19 '18 at 12:59

janek9971

114

Do you want to de-duplicate the ParentIds? I don't understand your train of thought and duplicate ids never seem to be a good idea.
– Marco
Nov 19 '18 at 13:04

This is not my DB but by coworker and ParentId can be duplicated knowingly.
– janek9971
Nov 19 '18 at 13:15

So you purposefully want duplicated ids as a result, yes?
– Marco
Nov 19 '18 at 13:17

My DB have unique PK ID, and parentid's which reference to PK ID. ParentId's can be duplicated and be null. Don't ask my why is that because it's not the case of my problem.
– janek9971
Nov 19 '18 at 13:19

add a comment |

Do you want to de-duplicate the ParentIds? I don't understand your train of thought and duplicate ids never seem to be a good idea.
– Marco
Nov 19 '18 at 13:04

This is not my DB but by coworker and ParentId can be duplicated knowingly.
– janek9971
Nov 19 '18 at 13:15

So you purposefully want duplicated ids as a result, yes?
– Marco
Nov 19 '18 at 13:17

My DB have unique PK ID, and parentid's which reference to PK ID. ParentId's can be duplicated and be null. Don't ask my why is that because it's not the case of my problem.
– janek9971
Nov 19 '18 at 13:19

Do you want to de-duplicate the ParentIds? I don't understand your train of thought and duplicate ids never seem to be a good idea.
– Marco
Nov 19 '18 at 13:04

This is not my DB but by coworker and ParentId can be duplicated knowingly.
– janek9971
Nov 19 '18 at 13:15

So you purposefully want duplicated ids as a result, yes?
– Marco
Nov 19 '18 at 13:17

My DB have unique PK ID, and parentid's which reference to PK ID. ParentId's can be duplicated and be null. Don't ask my why is that because it's not the case of my problem.
– janek9971
Nov 19 '18 at 13:19

add a comment |

1 Answer
1

active

oldest

votes

So you have a sequence of Categories, where every Category has an Id, a ParentId and a Name. You also have a string partOfName

You want the Names of Categories. All Categories? No. only those Categories that have a Parent which has a Name that looks like partOfName (looks like is represented by the use of the function contains in your code)

I'm not sure if you use entity framework. Your use of _context seems a hint to this. In that case, It would be easier to use the virtual parent properties. See later.

If you do not use entity framework, I'd advise to fetch your data in one query: join the elements with their parents, and keep only the join results where the parent meets your contains predicate:

string lowerCasePartOfName = partOfName.ToLower(); // for efficiency: do this only once

IQueryable<Category> categories = myDbContext.Categories;

IQueryable<Category> validParents = myDbContext.Categories

   .Where(category => category.Name.ToLower().Contains(lowerCasePartOfName))

Now join every Category with the validParentIds on Category.ParentId = Parent.Id

var query = Categories.Join(validParents, // join categories with validParents

    category => category.ParentId,         // from each category take the ParentId

    parent => parent.Id,                   // from each valid parent take the Id

    (category, parent) => new              // when they match make one new object

    {

        CategoryName = category.Name,

    });

Note, until now the query has been made, it has not been executed yet. If you want you can concatenate them into one big LINQ query. I'm not sure if that would increase performance very much; however, it would decrease readability.

var result = query.ToListAsync();

Entity Framework Solution

If you use entity framework, your Category class would be like:

class Category

{

     public int Id {get; set;}

     public string Name {get; set;}



     // every Category has zero or one Parent category:

     public int? ParentId {get; set;}

     public Category Parent {get; set;}



     // every Category has zero or more Children, each of them has this Category as Parent

     public virtual ICollection<Category> Children {get; set;}

}

You query will be much simpler:

Give me all names of Categories whose Parents have a name that looks like partOfName

string lowerCasePartOfName = partOfName.ToLower();

var result = myDbContext.Categories

    // keep only the Categories whose name looks like:

    .Where(category => category.Parent.Name.ToLower().Contains(lowerCasePartOfName))

    .Select(category => category.Name);

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

I use EF, but why you used Parent as a type which it is not a type. Name and ParentId are in the same table.
– janek9971
Nov 19 '18 at 15:40

Take a second look: Parent is the identifier of the Property. The type of the property is Category. In my entity framework example ParentId and Name are also in the same table. Next time, if you use entity framework, give us your classes. This helps us giving you an answer
– Harald Coppoolse
Nov 19 '18 at 15:58

Ok I see but still is not the case. You wrote "Give me all names of Categories whose Parents have a name that looks like partOfName". I want "Give me all categories that looks like partOfName and then add to every found category info about their parent name or set null if don't have parent" But thanks for your post tomorrow I try to that better.
– janek9971
Nov 19 '18 at 21:53

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375186%2flinq-search-database-considering-duplicates%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

So you have a sequence of Categories, where every Category has an Id, a ParentId and a Name. You also have a string partOfName

I'm not sure if you use entity framework. Your use of _context seems a hint to this. In that case, It would be easier to use the virtual parent properties. See later.

If you do not use entity framework, I'd advise to fetch your data in one query: join the elements with their parents, and keep only the join results where the parent meets your contains predicate:

string lowerCasePartOfName = partOfName.ToLower(); // for efficiency: do this only once

IQueryable<Category> categories = myDbContext.Categories;

IQueryable<Category> validParents = myDbContext.Categories

   .Where(category => category.Name.ToLower().Contains(lowerCasePartOfName))

Now join every Category with the validParentIds on Category.ParentId = Parent.Id

var query = Categories.Join(validParents, // join categories with validParents

    category => category.ParentId,         // from each category take the ParentId

    parent => parent.Id,                   // from each valid parent take the Id

    (category, parent) => new              // when they match make one new object

    {

        CategoryName = category.Name,

    });

var result = query.ToListAsync();

Entity Framework Solution

If you use entity framework, your Category class would be like:

class Category

{

     public int Id {get; set;}

     public string Name {get; set;}



     // every Category has zero or one Parent category:

     public int? ParentId {get; set;}

     public Category Parent {get; set;}



     // every Category has zero or more Children, each of them has this Category as Parent

     public virtual ICollection<Category> Children {get; set;}

}

You query will be much simpler:

Give me all names of Categories whose Parents have a name that looks like partOfName

string lowerCasePartOfName = partOfName.ToLower();

var result = myDbContext.Categories

    // keep only the Categories whose name looks like:

    .Where(category => category.Parent.Name.ToLower().Contains(lowerCasePartOfName))

    .Select(category => category.Name);

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

I use EF, but why you used Parent as a type which it is not a type. Name and ParentId are in the same table.
– janek9971
Nov 19 '18 at 15:40

Take a second look: Parent is the identifier of the Property. The type of the property is Category. In my entity framework example ParentId and Name are also in the same table. Next time, if you use entity framework, give us your classes. This helps us giving you an answer
– Harald Coppoolse
Nov 19 '18 at 15:58

Ok I see but still is not the case. You wrote "Give me all names of Categories whose Parents have a name that looks like partOfName". I want "Give me all categories that looks like partOfName and then add to every found category info about their parent name or set null if don't have parent" But thanks for your post tomorrow I try to that better.
– janek9971
Nov 19 '18 at 21:53

add a comment |

So you have a sequence of Categories, where every Category has an Id, a ParentId and a Name. You also have a string partOfName

I'm not sure if you use entity framework. Your use of _context seems a hint to this. In that case, It would be easier to use the virtual parent properties. See later.

If you do not use entity framework, I'd advise to fetch your data in one query: join the elements with their parents, and keep only the join results where the parent meets your contains predicate:

string lowerCasePartOfName = partOfName.ToLower(); // for efficiency: do this only once

IQueryable<Category> categories = myDbContext.Categories;

IQueryable<Category> validParents = myDbContext.Categories

   .Where(category => category.Name.ToLower().Contains(lowerCasePartOfName))

Now join every Category with the validParentIds on Category.ParentId = Parent.Id

var query = Categories.Join(validParents, // join categories with validParents

    category => category.ParentId,         // from each category take the ParentId

    parent => parent.Id,                   // from each valid parent take the Id

    (category, parent) => new              // when they match make one new object

    {

        CategoryName = category.Name,

    });

var result = query.ToListAsync();

Entity Framework Solution

If you use entity framework, your Category class would be like:

class Category

{

     public int Id {get; set;}

     public string Name {get; set;}



     // every Category has zero or one Parent category:

     public int? ParentId {get; set;}

     public Category Parent {get; set;}



     // every Category has zero or more Children, each of them has this Category as Parent

     public virtual ICollection<Category> Children {get; set;}

}

You query will be much simpler:

Give me all names of Categories whose Parents have a name that looks like partOfName

string lowerCasePartOfName = partOfName.ToLower();

var result = myDbContext.Categories

    // keep only the Categories whose name looks like:

    .Where(category => category.Parent.Name.ToLower().Contains(lowerCasePartOfName))

    .Select(category => category.Name);

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

I use EF, but why you used Parent as a type which it is not a type. Name and ParentId are in the same table.
– janek9971
Nov 19 '18 at 15:40

Take a second look: Parent is the identifier of the Property. The type of the property is Category. In my entity framework example ParentId and Name are also in the same table. Next time, if you use entity framework, give us your classes. This helps us giving you an answer
– Harald Coppoolse
Nov 19 '18 at 15:58

Ok I see but still is not the case. You wrote "Give me all names of Categories whose Parents have a name that looks like partOfName". I want "Give me all categories that looks like partOfName and then add to every found category info about their parent name or set null if don't have parent" But thanks for your post tomorrow I try to that better.
– janek9971
Nov 19 '18 at 21:53

add a comment |

So you have a sequence of Categories, where every Category has an Id, a ParentId and a Name. You also have a string partOfName

I'm not sure if you use entity framework. Your use of _context seems a hint to this. In that case, It would be easier to use the virtual parent properties. See later.

If you do not use entity framework, I'd advise to fetch your data in one query: join the elements with their parents, and keep only the join results where the parent meets your contains predicate:

string lowerCasePartOfName = partOfName.ToLower(); // for efficiency: do this only once

IQueryable<Category> categories = myDbContext.Categories;

IQueryable<Category> validParents = myDbContext.Categories

   .Where(category => category.Name.ToLower().Contains(lowerCasePartOfName))

Now join every Category with the validParentIds on Category.ParentId = Parent.Id

var query = Categories.Join(validParents, // join categories with validParents

    category => category.ParentId,         // from each category take the ParentId

    parent => parent.Id,                   // from each valid parent take the Id

    (category, parent) => new              // when they match make one new object

    {

        CategoryName = category.Name,

    });

var result = query.ToListAsync();

Entity Framework Solution

If you use entity framework, your Category class would be like:

class Category

{

     public int Id {get; set;}

     public string Name {get; set;}



     // every Category has zero or one Parent category:

     public int? ParentId {get; set;}

     public Category Parent {get; set;}



     // every Category has zero or more Children, each of them has this Category as Parent

     public virtual ICollection<Category> Children {get; set;}

}

You query will be much simpler:

Give me all names of Categories whose Parents have a name that looks like partOfName

string lowerCasePartOfName = partOfName.ToLower();

var result = myDbContext.Categories

    // keep only the Categories whose name looks like:

    .Where(category => category.Parent.Name.ToLower().Contains(lowerCasePartOfName))

    .Select(category => category.Name);

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

So you have a sequence of Categories, where every Category has an Id, a ParentId and a Name. You also have a string partOfName

I'm not sure if you use entity framework. Your use of _context seems a hint to this. In that case, It would be easier to use the virtual parent properties. See later.

If you do not use entity framework, I'd advise to fetch your data in one query: join the elements with their parents, and keep only the join results where the parent meets your contains predicate:

string lowerCasePartOfName = partOfName.ToLower(); // for efficiency: do this only once

IQueryable<Category> categories = myDbContext.Categories;

IQueryable<Category> validParents = myDbContext.Categories

   .Where(category => category.Name.ToLower().Contains(lowerCasePartOfName))

Now join every Category with the validParentIds on Category.ParentId = Parent.Id

var query = Categories.Join(validParents, // join categories with validParents

    category => category.ParentId,         // from each category take the ParentId

    parent => parent.Id,                   // from each valid parent take the Id

    (category, parent) => new              // when they match make one new object

    {

        CategoryName = category.Name,

    });

var result = query.ToListAsync();

Entity Framework Solution

If you use entity framework, your Category class would be like:

class Category

{

     public int Id {get; set;}

     public string Name {get; set;}



     // every Category has zero or one Parent category:

     public int? ParentId {get; set;}

     public Category Parent {get; set;}



     // every Category has zero or more Children, each of them has this Category as Parent

     public virtual ICollection<Category> Children {get; set;}

}

You query will be much simpler:

Give me all names of Categories whose Parents have a name that looks like partOfName

string lowerCasePartOfName = partOfName.ToLower();

var result = myDbContext.Categories

    // keep only the Categories whose name looks like:

    .Where(category => category.Parent.Name.ToLower().Contains(lowerCasePartOfName))

    .Select(category => category.Name);

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

answered Nov 19 '18 at 15:15

Harald Coppoolse

11.4k12959

I use EF, but why you used Parent as a type which it is not a type. Name and ParentId are in the same table.
– janek9971
Nov 19 '18 at 15:40

Take a second look: Parent is the identifier of the Property. The type of the property is Category. In my entity framework example ParentId and Name are also in the same table. Next time, if you use entity framework, give us your classes. This helps us giving you an answer
– Harald Coppoolse
Nov 19 '18 at 15:58

Ok I see but still is not the case. You wrote "Give me all names of Categories whose Parents have a name that looks like partOfName". I want "Give me all categories that looks like partOfName and then add to every found category info about their parent name or set null if don't have parent" But thanks for your post tomorrow I try to that better.
– janek9971
Nov 19 '18 at 21:53

add a comment |

I use EF, but why you used Parent as a type which it is not a type. Name and ParentId are in the same table.
– janek9971
Nov 19 '18 at 15:40

Take a second look: Parent is the identifier of the Property. The type of the property is Category. In my entity framework example ParentId and Name are also in the same table. Next time, if you use entity framework, give us your classes. This helps us giving you an answer
– Harald Coppoolse
Nov 19 '18 at 15:58

Ok I see but still is not the case. You wrote "Give me all names of Categories whose Parents have a name that looks like partOfName". I want "Give me all categories that looks like partOfName and then add to every found category info about their parent name or set null if don't have parent" But thanks for your post tomorrow I try to that better.
– janek9971
Nov 19 '18 at 21:53

I use EF, but why you used Parent as a type which it is not a type. Name and ParentId are in the same table.
– janek9971
Nov 19 '18 at 15:40

Take a second look: Parent is the identifier of the Property. The type of the property is Category. In my entity framework example ParentId and Name are also in the same table. Next time, if you use entity framework, give us your classes. This helps us giving you an answer
– Harald Coppoolse
Nov 19 '18 at 15:58

Ok I see but still is not the case. You wrote "Give me all names of Categories whose Parents have a name that looks like partOfName". I want "Give me all categories that looks like partOfName and then add to every found category info about their parent name or set null if don't have parent" But thanks for your post tomorrow I try to that better.
– janek9971
Nov 19 '18 at 21:53

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu