none
Soo close on converting SQL Query to LINQ. Please Help. RRS feed

  • Question

  • I have the following SQL Query with two Left Joins. I am sooo close but I need to address a small portion of the SQL:

    Select UserId, Store, GroupName from appusers au 
    left join storegroups sg on au.stores = sg.groupname 
    left join stores s on (isnumeric(au.stores) = 1 and au.stores = s.store) or sg.store = s.store

    The part I need to address is:

    (isnumeric(au.stores) = 1 and au.stores = s.store) or

    A friend and I have worked on this for hours and this is what we came up with:

    from au in AppUsers
    join sg in StoreGroups on au.Stores equals sg.GroupName into t
    from sg1 in t.DefaultIfEmpty()
    join s in Stores on sg1.Store equals s.Store into h
    from st in h.DefaultIfEmpty()
    select new{ user=au.UserID, StoreId = st.Store == null ? int.Parse(au.Stores == "All Stores" ? "0":au.Stores) : st.Store, st.ShortName }
    Any help is appreciated!
    Thursday, April 19, 2012 3:23 AM

Answers

  • Hi David

    maybe ,finnal statement query .you can try where statement.

    for example

    from s in Stores
    jion sg in StoreGroups on s.Store equals sg.Store into ss
    from _s in ss.DefaultIfEmpty()
    from au in AppUsers on au.Store
    where (au.Stores == s.Store && _s==null) || (_s != null && _s.GroupName == s.Stores)
    || (au.Stores == "All Stores" && _s==null)

    select new {s,au}

    good luck.


    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

    Friday, April 20, 2012 9:44 AM

All replies

  • hi ,

    maybe,as possible as  you can show us the models and the relationship of entities ,AppUsers,StoreGroups,Stores.

    or some else more details


    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.


    Thursday, April 19, 2012 6:11 AM
  • Sure. :)

        public partial class AppUser
        {
            public string UserID { get; set; }
            public string Password { get; set; }
            public short AuthorityLevel { get; set; }
            public string Stores { get; set; }
            public string Area { get; set; }
        } 
    
        public partial class StoreGroup
        {
            public string GroupName { get; set; }
            public int Store { get; set; }
        }
    
       public partial class Store
        {
            public short Client { get; set; }
            public int Store { get; set; }
            public string Name { get; set; }
            public string Address1 { get; set; }
            public string Address2 { get; set; }
            public string City { get; set; }
            public string State { get; set; }
            public string Zip_Code { get; set; }
            public string ShortName { get; set; }
    
        }

    AppUsers.Stores could contain three types of values.

    1) A Store Number (i.e. 136) - In this case the appuser table is joined to the stores table (IsNumeric(au.Stores) and au.Stores = S.Store) to provide the ShortName of the store.

    2) A GroupName (i.e. "Culp Group") - In this case, AppUsers is joined to StoreGroups (au.Stores = SG.GroupName), which serves as a Bridge table for the one-to-many and then joined to the Stores table (SG.Store = S.Store).

    3) The string "All Stores" - In this case the user naturally has access to all stores and it should return a store number of -1 or null.


    I would expected the output to be something like this:
    userId               GroupName                      Store       ShortName
    -------------------- ------------------------------ ----------- --------------------
    ADMIN                All Open CZ                    11          CZ11 - Lubbock TX
    ADMIN                All Open CZ                    15          CZ15 - Neosho MO
    ADMIN                All Open CZ                    22          CZ22 - Joplin MO
    ADMIN                All Open CZ                    31          CZ31 - Stillwater OK
    ADMIN                All Open CZ                    33          CZ33 - ElPaso TX
    ADMIN                All Open CZ                    34          CZ34 - Grove OK
    ADMIN                All Open CZ                    35          CZ35 - Atlanta GA
    ALISSA               NULL                           136         SSP136 - Diboll TX
    BECKY                All Open                       11          CZ11 - Lubbock TX
    BECKY                All Open                       15          CZ15 - Neosho MO
    BECKY                All Open                       22          CZ22 - Joplin MO
    BECKY                All Open                       31          CZ31 - Stillwater OK
    BECKY                All Open                       33          CZ33 - ElPaso TX
    BECKY                All Open                       34          CZ34 - Grove OK
    BECKY                All Open                       35          CZ35 - Atlanta GA
    BECKY                All Open                       115         SSP115 - Granby MO
    BECKY                All Open                       136         SSP136 - Diboll TX
    BECKY                All Open                       10015       SSP15 - Pryor OK
    CHARLOTTE            NULL                           34          CZ34 - Grove OK
    CHUCK                NULL                           10015       SSP15 - Pryor OK
    DARRYL               NULL                           NULL        NULL
    DAVEG                NULL                           NULL        NULL

    • Edited by DavidGerler Thursday, April 19, 2012 12:09 PM
    Thursday, April 19, 2012 12:06 PM
  • Hi David

    maybe ,finnal statement query .you can try where statement.

    for example

    from s in Stores
    jion sg in StoreGroups on s.Store equals sg.Store into ss
    from _s in ss.DefaultIfEmpty()
    from au in AppUsers on au.Store
    where (au.Stores == s.Store && _s==null) || (_s != null && _s.GroupName == s.Stores)
    || (au.Stores == "All Stores" && _s==null)

    select new {s,au}

    good luck.


    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

    Friday, April 20, 2012 9:44 AM