Question

As new to Ling having a bit of a problem see how you do the following query

I want all topics which have a subscription with an ApplicationName of "name"

Topic -> Subscriptions -> Subscription

started to write

var topics = from topic in Query.All<topic>() where topic.Subscriptions...... not clear how to continue

I think I missing something pretty basic, could you point me in the right direction

[HierarchyRoot]
    [Index("Name")]
    public class Topic : Entity {
        [Field, Key]
        public long Id { get; private set; }

        [Field]
        public string Name { get; set; }

        [Field, Association(PairTo = "Topic", OnTargetRemove = OnRemoveAction.Clear)]
        public EntitySet<Event> Events { get; private set; }

        [Field, Association(OnTargetRemove = OnRemoveAction.Clear)]
        public EntitySet<Subscription> Subscriptions { get; private set; }
    }
    [HierarchyRoot]
    [Index("TimeStamp")]
    [Index("ApplicationName")]
    public class Event : Entity {

        public Event() {
            TimeStamp = DateTime.Now;
        }

        [Field, Key]
        public long Id { get; private set; }

        [Field]
        public DateTime TimeStamp { get; private set; }

        [Field]
        public String ApplicationName { get; set; }

        [Field]
        public Topic Topic { get; set; }
  }

    [HierarchyRoot]
    [Index("ApplicationName")]
    public class Subscription : Entity {
        [Field, Key]
        public long Id { get; private set; }

        [Field]
        public string ApplicationName { get; set; }

        [Field]
        public bool Persistent { get; set; }

        [Field]
        public Event LastEvent { get; set; }
    }

This thread was imported from our support forum. The original discussion may contain more detailed answer.

Answer 1

There are set of ways:

var subscriptions = 
  from topic in Query.All<Topic>()
  from subscription in topic.Subscriptions
  where subscription.ApplicationName==applicationName
  select subscription;

var pairs = 
  from topic in Query.All<Topic>()
  from subscription in topic.Subscriptions
  where subscription.ApplicationName==applicationName
  select new {Topic = topic, Subscription = subscription};

var subscriptions = 
  from topic in Query.All<Subscription>()
  where subscription.ApplicationName==applicationName &&
    (from topic in Query.All<Topic>()
    where topic.Subscriptions.Contains(subscription)
    select "ThisValueHasNoMeaningAnyway").Any() // Reverse lookup
  select subscription;

var topicsWithGoodSubscriptions = 
  from topic in Query.All<Topic>()
  let goodSubscriptions = topic.Subscriptions.Where(s => s.ApplicationName==applicationName)
  where goodSubscriptions.Any()
  select new {Topic = topic, GoodSubscriptions = goodSubscriptions}; 
  // GoodSubscriptions will be IQueryable<Subscription>, so you can query it further;
  // GoodSubscriptions materialization will lead to batched subqueries. Take a look at executed SQL here - 
  // you'll find subqueries are executed on materialization in batches.

So LINQ is nice ;)

Btw, likely the first query will be the most efficient one. I recommend you to check out query plans for them, if you're worrying about performance.

Subscription:

Related questions