0

I've tried to figure it out why it is slow when I join 4 tables together by using Linq in C#. Furthermore, there are two the same tables wp_posts. I would like to know if I could reduce that tables it might be faster. I am using MySQL. 

var results = from a in _newsEntities.wp_posts
            join b in _newsEntities.wp_postmeta
            on a.ID equals b.post_id
            join c in _newsEntities.wp_posts
            on b.meta_value equals c.ID.ToString()
            join d in _newsEntities.wp_users on a.post_author equals d.ID
            where a.post_status == "publish" &&
                  a.post_type == "post" &&
                  b.meta_key == "reporter_name"
            select new
            {
                Id = a.ID,
                Status = a.post_status,
                Content = a.post_content,
                ReporterId = c.post_content,
                ReporterName = c.post_title,
                DateCreated = a.post_date,
                DateModified = a.post_modified,
                AuthorId = a.post_author,
                AuthorName = d.display_name
            };
Seanghay
  • 1,076
  • 1
  • 11
  • 24
  • The fact that you have to do a `ToString` in one join isn't a good sign. I'd suggest profiling the resulting SQL and determine any indexes that would help. – juharr Jul 05 '17 at 01:54
  • So, how can I convert `c.ID` to string or `b.meta_value` to decimal? Thanks! – Seanghay Jul 05 '17 at 02:02
  • I'm guessing your meta data has more than ids in it so you cannot change your DB so that `meta_value` isn't a string. So your just stuck with having to do a conversion in a join which will be less efficient. – juharr Jul 05 '17 at 02:08
  • 1
    Would something like this reduce the time taken: `from a in _newsEntities.wp_posts join b in _newsEntities.wp_postmeta on new { ID = a.ID, m_val = a.ID.ToString() } equals new { ID = b.post_id, m_val = b.meta_value }`? Basically [from here](https://stackoverflow.com/questions/39567222/how-to-write-linq-for-multiple-joins-involving-self-join). – Keyur PATEL Jul 05 '17 at 02:10

0 Answers0