CODEWITHSUNDEEP
.netlinqentity-framework
user441365
user441365

Reputation: 4024

multiple similar linq queries

I have a model Course, which has several many to many relationships like Age or Time.

I have this query:

string IDs = "1,2,3"
string[] IDList = IDs.Split(',');

return (from x in entities.Course
       where x.Ages.Where(val => IDList.Contains(val.ID.ToString())).Count() == IDList.Count()
       select x);

And I need to set the same query for Time and several other properties as in:

string IDs = "1,2,3"
string[] IDList = IDs.Split(',');

return (from x in entities.Course
       where x.Times.Where(val => IDList.Contains(val.ID.ToString())).Count() == IDList.Count()
       select x);

How can I make the query more dynamic so I don't have multiple similar queries?

Thanks

Upvotes: 3

Views: 80

Answers (2)

joe_coolish
joe_coolish

Reputation: 7259

You could make a method that accepts an Expression (depeneding on your data type) and run the query that way. You'll need to make your Ages, Time, etc implement a specific interface for it to work.

For example, assuming that you are using EF and your model is Code First using DbSets, you could make this:

public interface IObject
{
    int ID { get; set; }
}

public class Age : IObject
{
    public int ID { get; set; }

    // The rest of the data
}

public class Time : IObject
{
    public int ID { get; set; }

    // The rest of the data
}

public class Course
{
    public virtual ICollection<Age> Ages { get; set; }
    public virtual ICollection<Time> Times { get; set; }
}

public class CourseContext : DbContext
{
    public DbSet<Course> Course { get; set; }
}

public class Test
{
    public IQueryable<Course> GetCourses(Expression<Func<Course, ICollection<IObject>>> exp)
    {
        var entities = new CourseContext(); 
        string IDs = "1,2,3";
        string[] IDList = IDs.Split(',');

        var c = exp.Compile();

        return entities.Course.Where(x => c.Invoke(x).Count(val => IDList.Contains(val.ID.ToString())) == IDList.Count());
    }

    public void TestMethod()
    {
        var times = GetCourses(c => (ICollection<IObject>)c.Times);
        var ages = GetCourses(c => (ICollection<IObject>)c.Ages);
    }
}

Upvotes: 2

Yuck
Yuck

Reputation: 50835

I'd make methods that return the different query results:

public IQuerable<Course> GetAllCourses() {
    return entities.Course;
}

public IQueryable<Course> ByAge(IQueryable<Course> source, IEnumerable<String> ages {
    return from x in source
           where x.Ages.Where(val => ages.Contains(val.ID.ToString())).Count() == IDList.Count()
           select x;
}

public IQuerable<Course> ByTimes(IQueryable<Course> source, IEnumerable<String> times) {
    return from x in source
           where x.Ages.Where(val => IDList.Contains(val.ID.ToString())).Count() == IDList.Count()
           select x;
}

The reason is that the method encapsulates your query logic - only the where clause is different. You can then pass in any source. You could even combine the two query filters as a chained method call:

var ids = new [] { "1", "2", "3" };
var coursesByAgeAndTime = ByTime(ByAge(GetAllCourses(), ids), ids);

Upvotes: 1

Related Questions