ASP.Net MVC converting Sql to Linq

Question

I'm updating an old app, to use EF and Linq. I'm having trouble with one of the queries - in SQL it is:

SELECT    id, type_id, rule_name, rule_start, rule_end, rule_min
FROM      Rules
WHERE     (rule_min > 0) 
          AND (rule_active = 1) 
          AND (rule_fri = 1) 
          AND ('2012-01-01' BETWEEN rule_start AND rule_end) 
          AND (id IN
                      (SELECT      rule_id
                        FROM       RulesApply
                        WHERE      (type_id = 3059)))
ORDER BY pri

So far I have:

         var rules = db.Rules.Include("RulesApply")
                .Where(t => (t.rule_active == 1)
                    && (t.rule_min > 0)
                    && (dteFrom >= t.rule_start && dteFrom <= t.rule_end)
                    && (this is where I'm stuck)
                    )
                    .OrderBy(r => r.pri);

It's the last subquery I'm stuck with adding into the LINQ above:

AND (id IN
(SELECT      rule_id
FROM       RulesApply
WHERE      (type_id = 3059)))

Models are:

public class Rule
{
    [Key]
    public Int64 id { get; set; }
    public Int64 hotel_id { get; set; }
    public byte rule_active { get; set; }
    public DateTime rule_start { get; set; }
    public DateTime rule_end { get; set; }
    public int rule_min { get; set; }
    public int pri { get; set; }
    public virtual ICollection<RuleApply> RulesApply { get; set; }
}

public class RuleApply
{

    [Key, Column(Order = 0)]
    public Int64 type_id { get; set; }
    [Key, Column(Order = 1)]
    public Int64 rule_id { get; set; }
    [ForeignKey("rule_id")]
    public virtual Rule Rule { get; set; }
}

Can anyone please help me complete this query?

Thank you,

Mark

Corey Adler · Accepted Answer · 2013-03-12 14:55:21Z

2

Try doing this:

var rules = db.Rules.Include("RulesApply")
                .Where(t => (t.rule_active == 1)
                    && (t.rule_min > 0)
                    && (dteFrom >= t.rule_start && dteFrom <= t.rule_end)
                    && t.RulesApply.Any(a => a.type_id == 3059)
                    .OrderBy(r => r.pri);

If t.RulesApply is illegal (i.e. doesn't compile), then replace it with the correct reference to the navigation property found on your Rules object that points to the RulesApply object.

edited Mar 12, 2013 at 14:55

answered Mar 12, 2013 at 14:16

Corey Adler

16.2k18 gold badges70 silver badges80 bronze badges

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

1 Comment

Mark Over a year ago

Thanks - intellisense for t.RulesApply. doesn't come up with any properties, just Count, Order, First etc etc. What do you mean by correct reference to the navigation property? The database table is old, and doesn't have a primary key, hence adding the [key, column...] to the rulesapply able. Thanks again, Mark

Flater · Accepted Answer · 2013-03-12 15:01:22Z

2

If you have set up navigational properties between the entities, you can navigate from one to the other:

//This gets the RulesApply object
var rulesapply = db.RulesApply.Single(x=> x.type_id == 3059);

//This gets all Rules connected to the rulesapply object through its navigational property
var rules = rulesapply.Rules;

//You can use LINQ to further refine what you want
rules = rules.Where( x=> /* and so on...*/ );

You can stack these statements together on a single line, I only split them up for readability purposes :)

answered Mar 12, 2013 at 15:01

Flater

14.1k4 gold badges45 silver badges67 bronze badges

Collectives™ on Stack Overflow

ASP.Net MVC converting Sql to Linq

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related