Using an list in a query in entity framework

Question

I am trying to find a way to pass in an optional string list to a query. What I am trying to do is filter a list of tags by the relationship between them. For example if c# was selected my program would suggest only tags that appear in documents with a c# tag and then on the selection of the next, say SQL, the tags that are linked to docs for those two tags together would be shown, whittling it down so that the user can get closer and closer to his goal.

At the moment all I have is:

List<Tag> _tags =  (from t in Tags
                   where t.allocateTagDoc.Count > 0
                   select t).ToList();

This is in a method that would be called repeatedly with the optional args as tags were selected.

I think I have been coming at it arse-backwards. If I make two(or more) queries one for each supplied tag, find the docs where they all appear together and then bring out all the tags that go with them... Or would that be too many hits on the db? Can I do it entirely through an entity context variable and just query the model?

Thanks again for any help!

Yograj Gupta · Accepted Answer · 2012-07-18 05:18:59Z

1

You can try this.

First collect tag to search in a list of strings .

List<string> tagStrings = new List<string>{"c#", "sql"};

pass this list in your query, check whether it is empty or not, if empty, it will return all the tags, else tags which matches the tagStrings.

var _tags = (from t in Tags 
             where t.allocateTagDoc.Count > 0
             && (tagStrings.Count ==0  || tagStrings.Contains(t.tagName))
             select t).ToList();

answered Jul 18, 2012 at 5:18

Yograj Gupta

9,8673 gold badges31 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The_Cthulhu_Kid Over a year ago

Would it be easier if, instead of strings, I were to pass in the ID's from tags?

Yograj Gupta Over a year ago

Yes, you can pass ids there, Ids will preform well in db query.

Ivan Golović · Accepted Answer · 2012-07-18 08:31:00Z

You can also try this, Dictionary represents ID of a document with it's tags:

        Dictionary<int, string[]> documents = 
            new Dictionary<int, string[]>();

        documents.Add(1, new string[] { "C#", "SQL", "EF" });
        documents.Add(2, new string[] { "C#", "Interop" });
        documents.Add(3, new string[] { "Javascript", "ASP.NET" });
        documents.Add(4, new string[] { });

        // returns tags belonging to documents with IDs 1, 2
        string[] filterTags = new string[] { "C#" };
        var relatedTags = GetRelatedTags(documents, filterTags);
        Debug.WriteLine(string.Join(",", relatedTags));

        // returns tags belonging to document with ID 1
        filterTags = new string[] { "C#", "SQL" };
        relatedTags = GetRelatedTags(documents, filterTags);
        Debug.WriteLine(string.Join(",", relatedTags));

        // returns tags belonging to all documents 
        // since no filtering tags are specified
        filterTags = new string[] { };
        relatedTags = GetRelatedTags(documents, filterTags);
        Debug.WriteLine(string.Join(",", relatedTags));


    public static string[] GetRelatedTags(
        Dictionary<int, string[]> documents, 
        string[] filterTags)
    {
        var documentsWithFilterTags = documents.Where(o => 
            filterTags
                .Intersect(o.Value).Count() == filterTags.Length);

        string[] relatedTags = new string[0];

        foreach (string[] tags in documentsWithFilterTags.Select(o => o.Value))
            relatedTags = relatedTags
                .Concat(tags)
                .Distinct()
                .ToArray();

        return relatedTags;
    }

The thing is I am going through an existing db where the tags are already assigned (or will be assigned by the users). Although the dictionary is definitely a good idea. Thanks!

The_Cthulhu_Kid · Accepted Answer · 2012-09-14 17:54:25Z

Thought I would pop back and share my solution which was completely different to what I first had in mind.

First I altered the database a little getting rid of a useless field in the allocateDocumentTag table which enabled me to use the entity framework model much more efficiently by allowing me to leave that table out and access it purely through the relationship between Tag and Document.

When I fill my form the first time I just display all the tags that have a relationship with a document. Using my search filter after that, when a Tag is selected in a checkedListBox the Document id's that are associated with that Tag(s) are returned and are then fed back to fill the used tag listbox.

public static List<Tag> fillUsed(List<int> docIds = null)
        {
            List<Tag> used = new List<Tag>();

            if (docIds == null || docIds.Count() < 1)
            {
                used = (from t in frmFocus._context.Tags
                        where t.Documents.Count >= 1
                        select t).ToList();  
            }
            else
            {    
                used = (from t in frmFocus._context.Tags
                        where t.Documents.Any(d => docIds.Contains(d.id))
                        select t).ToList();  
            }
            return used;
        }

From there the tags feed into the doc search and vice versa. Hope this can help someone else, if the answer is unclear or you need more code then just leave a comment and I'll try and sort it.

Collectives™ on Stack Overflow

Using an list in a query in entity framework

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related